SQLTeam.com | Weblogs | Forums

Triggers in SQL Server

sql2014
#1

Hello,

I am new to SQL Server but I have experience of Oracle and SQL Anywhere. Something you can do in both Oracle and SQL Anywhere is to create an after Trigger for insert, update and delete (one trigger for them all) and check inside the trigger if it is an insert, update or delete. If I have understood it correctly this is not possible in SQL Server, instead I have to create three triggers, is this correct?

In the previous databases mentioned above you can also choose to have the trigger for each row, but this is not possible for SQL Server, here it is per transaction instead? How do you handle if several rows have been updated in one transaction and each of the rows should trigger an event?

I might have missunderstood something and f this is the case please be so kind to guide me in the correct direction

M.G

#2

You can have a single trigger for any combination of INSERT, UPDATE and/or DELETE. See the syntax here.

Triggers are fired once per update/delete/insert statement regardless of how many rows are affected. Not once per transaction.

Within the trigger, you have access to two virtual tables INSERTED and DELETED. Those have the after and before state of the rows that have been affected.

If INSERTED is empty, that means it is a delete operation; if DELETED is empty, it is an insert operation. If both tables have non-zero number of records, it is an update operation.

You can examine the rows in the INSERTED and/or DELETED tables and do whatever you may need to do to each row. The beauty is that this method allows you to do the operations in a set based manner on all the affected rows.

#3

Hi, thanks for your answer

But I have some more uncertainties, hope perhaps you can clarify

If you assume that you handle each row independently in a trigger, can't this leads to errors? Assuming that the inserts and deleted tables always only contain one row,when they actually can contin many?

How does the insert/delete table look, is this dependent on the original table itself (table where the update/insert/delete was made) or is it dependent on the colums updated/inserted/deleted of the original table?

M.G

#4

Unless you are writing an "INSTEAD OF" trigger (which I am not particularly a fan of), more often than not, you don't want to do anything to the rows in the table that you are acting on in the trigger. The best use-case for triggers is for auditing. If you use it for other purposes, make it short and sweet.

INSERTED and DELETED tables will have exactly the same columns as the table on which you are doing the upsert.

Create a test table like shown below in your database and experiment with it.

CREATE TABLE dbo.T1 (Id INT, val VARCHAR(32));
GO
CREATE TRIGGER T1_Trigger
ON dbo.T1 FOR INSERT, UPDATE, DELETE
AS 
	SELECT 'DELETED', * FROM DELETED;
	SELECT 'INSERTED', * FROM INSERTED;
GO

INSERT INTO dbo.T1 VALUES (1,'A');
INSERT INTO dbo.T1 VALUES (2,'B');
UPDATE dbo.T1 SET val = 'C';
DELETE FROM dbo.T1 WHERE id = 2;

GO
DROP TABLE dbo.T1;
GO
#5

Yes. That is what James is pointing out: you cannot ever make that assumption in SQL Server. You must always code the trigger to handle multiple rows at once. Period. Also, SQL Server does not support BEFORE triggers, only AFTER (or INSTEAD OF) triggers.

It's very common to update the original table in a trigger, either to fill in "special" default values or to flag/unflag certain records.

Most often it's best to write separate triggers for each action to keep triggers as small and efficient as possible, but you can write a combined trigger if you prefer.

Finally, you should never attempt to return a result set or any other data from a trigger (PRINT ... or SELECT ... FROM ... (rather than SELECT @ ... = ... FROM ...). That's not officially supported and will certainly be removed in a later version of SQL Server. Write to a table or use some other method to capture what you need.