SQL Server Portal

Whenever you upgrade previous version of SQL Server to SQL Server 2012 you need to take care of few issues. In this article, I will discuss one of the important issues that is Create trigger with append clause. This clause will not stop you to upgrade it to SQL Server 2012 but it will break your scripts. But how ?
Let me explain it Step by Step with the simple examples.
Step 1 :
First create given below table and triggers on any database prior to SQL Server 2012.

CREATE TABLE dbo.Student
([Student_ID] INT,
[Student_Name] VARCHAR(100)
)
GO
CREATE TRIGGER dbo.FirstTrigger
ON dbo.Student
FOR INSERT
AS
BEGIN
PRINT 'First Trigger'
END
GO
CREATE TRIGGER dbo.SecondTrigger
ON dbo.Student
FOR INSERT
WITH APPEND
AS
BEGIN
PRINT 'Second Trigger'
END
GO

Step 2 :
Upgrade the database on SQL Server 2012. Once you upgrade it will not alert you.
Step 3 :
Immediately after upgrade, run the given below query to view if any with append clause is available in the trigger.

Select A.object_id,A.definition from sys.sql_modules A
Inner Join sys.triggers B On A.object_id =B.object_id
where definition like '%with append%'

Step 4 :
In the above example, we have one trigger with append clause, so alter the trigger and remove the with append clause because it is no more required for the triggers in latest versions of SQL Server.

Now, you are ready to use this trigger in the SQL Server 2012.

In case, if you don’t follow step 3 and 4, lets see what may happen.

insert into student values (1,'Imran')
--OUTPUT

First Trigger
Msg 195, Level 15, State 1, Procedure SecondTrigger, Line 5
‘APPEND’ is not a recognized option.

Conclusion :
Remember, whenever you upgrade to SQL Server 2012, check WITH APPEND clause  in the triggers to avoid this error.

Read Full Post »