Rows affected and Trigger Firing

SQL Server fires triggers whenever it executes a DML statemenet regardless there are records inserted/deleted/updated by the statement. E.g.:
CREATE TABLE dbo.TestTrg (c1 int not null,c2 int not null PRIMARY KEY CLUSTERED(c1))
GO
CREATE TRIGGER dbo.TR_TestTrg_Delete ON dbo.TestTrg FOR DELETE
AS
PRINT ‘Trigger fired’
SELECT COUNT(*) AS RowDeleted FROM deleted
GO
DELETE dbo.TestTrg WHERE c1<0
GO
The above delete statement will fire the delete trigger.
In some scenario, you may get unexpected errors by this. E.g.:
CREATE TABLE dbo.TestTrg1 (c1 int not null)
GO
ALTER TRIGGER dbo.TR_TestTrg_Delete ON dbo.TestTrg FOR DELETE
AS
PRINT ‘Trigger fired’
DECLARE @c1 int
SELECT TOP 1 @c1=c1 FROM deleted
INSERT dbo.TestTrg1 (c1) values (@c1)
GO
DELETE dbo.TestTrg WHERE c1<0
You get an error saying column c1 does not allow null. This is because deleted table is empty and @c1 is null.
To prevent this type of unexpected error, the best practice is always check inserted/deleted has records or not.
e.g.:
ALTER TRIGGER dbo.TR_TestTrg_Delete ON dbo.TestTrg FOR DELETE
AS
IF EXISTS(SELECT 1 FROM deleted)
BEGIN
 PRINT ‘Trigger fired’
 DECLARE @c1 int
 SELECT TOP 1 @c1=c1 FROM deleted
 INSERT dbo.TestTrg1 (c1) values (@c1)
END
GO
Now run the DELETE statement:
DELETE dbo.TestTrg WHERE c1<0
It’s succeeded.
INSERT dbo.TestTrg(c1,c2) VALUES (1,2)
GO
DELETE dbo.TestTrg WHERE c1=1
GO
You can verify trigger is fired and data is populated into dbo.TestTrg1 successfully by:
SELECT * FROM dbo.TestTrg1
GO
 
Advertisements
This entry was posted in SQL server. Bookmark the permalink.

Leave a Reply

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