#0161 – SQL Server 2012–Deprecated Features-CREATE TRIGGER-WITH APPEND option


There would hardly be a database developer who hasn’t written triggers in their career. It is fairly common knowledge that you can have more than one trigger of the same type, i.e. INSERT/UPDATE/DELETE on the same table. Therefore, the following is perfectly valid:

USE tempdb
GO
CREATE TABLE dbo.AppendTriggerTest (RowId INT IDENTITY(1,1),
                                    RowValue VARCHAR(50)
                                   )
GO

CREATE TRIGGER dbo.MainTrigger
ON dbo.AppendTriggerTest
FOR INSERT
AS
BEGIN
  PRINT '<<<< INFO >>>> Main Trigger fired...'
END
GO

CREATE TRIGGER dbo.AppendTrigger
ON dbo.AppendTriggerTest
FOR INSERT
AS
BEGIN
  PRINT '<<<< INFO >>>> Append Trigger fired...'
END
GO

However, this was not the case in the days of SQL Server 6.5. In order to have a more than one trigger of the same type on the same table, the WITH APPEND clause needs to be used, which is effectively interpreted as AFTER. In the days of SQL Server 6.5, the trigger “AppendTrigger” will need to be defined as:

CREATE TRIGGER dbo.AppendTrigger
ON dbo.AppendTriggerTest
FOR INSERT
WITH APPEND     --NOTICE THE WITH APPEND CLAUSE HERE, 
                --DEPRECATED FROM SQL 2012 ONWARDS
AS
BEGIN
  PRINT '<<<< INFO >>>> Append Trigger fired...'
END
GO

Starting SQL Server 2012, the compatibility levels lower than 90 (i.e. SQL Server 2000 and below) are no longer supported. In accordance to this, the WITH APPEND clause has also been deprecated.

I assume that you are not using this in your production code, if you are, please upgrade! There is no workaround/alternate to this.

Reference:

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s