Implementing Triggers…Part 1
A tigger is an implementation of code that runs in response to an event that takes place in a database. There are two types of triggers: Data Manipulation Language (DML), which run when INSERT, UPDATE, or DELETE statements modify data in a specified table or view. Data Definition Language (DDL), which run in response to DDL events on the server such as creating, altering, or dropping an object, are using for database administration tasks such as auditing, and controlling object access. Part 1 of Implementing triggers will focus on using DML’s in SQL Server.
Data Manipulation Language (DML) — Unlike SP’s and Functions DML Triggers are not stand-alone objects, and you cannot directly execute them. A DML trigger is attached to a specific table or view and defined for a particular event. The events that can cause a DML Trigger to fire are Insert, Update, and Delete. Triggers can fire in two different modes: AFTER and INSTEAD OF. In AFTER mode the trigger will be fired after the event that started the trigger is commpleted. In INSTEAD OF mode the trigger will be fired instead of the event’s code. The most common use of a INSTEAD OF trigger is to update the base tables through a view. You can only define one INSTEAD OF trigger for each INSERT, UPDATE, or DELETE event for a view or table.
The code within a a trigger can have any statements and constructs valid for a batch except for the following:
Database cannot be created, altered, dropped, backed up, or restored.
Structural changes cannot be made to the table that cause the trigger to fire such as CREATE/ALTER/DROPINDEX, ALTER/DROPTABLE, etc…
Triggers have access to two special tables that are dynamically generated, the INSERTED and DELETED tables. The structure of the the INSERTED and DELETED tables excactly matches the column definition of the table on which the trigger was created. This means that you can reference columns by using the same name as the table for whith the trigger was defined. When you execute an INSERT operation, the INSERTED table contains each row that was inserted into the table, whereas the DELETED table does not contain any rows. When you execute a DELETE statement, the DELETED table contains each row that was deleted from the table, and the INSERT table doesn’t contain any rows. When you execute and update statement the INSERTED table contains the after image of each row you updated.
Some syntax rules for a DML are below:
- Every trigger must have a name that conforms to the rules for object identifiers.
- You use the ON clause to specify the table or view that the trigger will be created against.
- Using the WITH clause you can specify whether the code will be encrypted when executing and specify an execution context.
- The FOR clause specifies whether or not the trigger is an AFTER or INSTEAD OF as well as events that cause the trigger to fire.
- Following the AS clause you specify the code that you want to execute when the trigger is fired.
Now with that said lets look at and example: HR as a strict policy that requires any changes to an employees pay rate to be audited. The audit must include the following:
- Prior Pay Rate
- Date the change was made
- Current Pay Rate
- Name of the person who made the change
Here is and example code of how to achieve this:
CREATE TRIGGER tu_employeepayaudit
ON dbo.Employee
FOR UPDATE
AS
DECLARE @now DATETIME
SET @now = GETDATE()
BEGIN TRY
INSERT INTO dbo.EmployeeAudit
(RowImage, PayRate, ChangeDate, ChangeUser)
SELECT ‘BEFORE’, DELETED.PayRate, @now, suser_sname()
FROM DELETED
INSERT INTO dbo.EmployeeAudit
(RowImage, PayRate, ChangeDate, ChangeUser)
SELECT ‘AFTER’, DELETED.PayRate, @now, suser_sname()
FROM INSERTED
END TRYBEGIN CATCH
–You can place some error handling code here
ROLLBACK TRANSACTION
END CATCH
