Since the trigger trgEncrypted is created with the option WITH ENCRYPTION, the trigger definition is hidden and there is no way that one can easily decrypt the trigger code.
We all know that the DML statements change or modify data. Sometimes it becomes necessary for the triggers to have the access to the changes being caused by the DML statements. SQL Server 2000 provides four different ways to determine the affects of the DML statements. The INSERTED and DELETED tables, popularly known as MAGIC TABLES, and update () and columns_updated() functions can be used to determine the changes being caused by the DML statements.
The below table depicts the contents of the INSERTED and DELETED tables for three different table Events
|
EVENT |
INSERTED |
DELETED |
|
Insert |
Contains the inserted rows |
Empty |
|
Delete |
Empty |
Contains the rows to be deleted |
|
Update |
Contains the rows after update |
Contains the rows before update |
Note that the Magic Table does not contain the information about the columns of the data-type text, ntext, or image. Attempting to access these columns will cause an error.
The update() function is used to find whether a particular column has been updated or not. This function is generally used for data checks.
CREATE TRIGGER trgUddate
ON User_Details
FOR UPDATE
AS
If UPDATE(FName)
BEGIN
PRINT(‘AFTER Trigger [trgUddate] – Executed – First Name has been updated’)
ROLLBACK TRANSACTION
END
else If UPDATE(LName)
BEGIN
PRINT(‘AFTER Trigger [trgUddate] – Executed – Last Name has been updated’)
ROLLBACK TRANSACTION
END
else If UPDATE(MName)
BEGIN
PRINT(‘AFTER Trigger [trgUddate] – Executed – MName Name has been updated’)
ROLLBACK TRANSACTION
END
else If UPDATE(Email)
BEGIN
PRINT(‘AFTER Trigger [trgUddate] – Executed – Email has been updated’)
ROLLBACK TRANSACTION
END
GO
UPDATE User_Details
SET MName = ‘Diwaker’
WHERE UserID = 1
Output
AFTER Trigger [trgUddate] – Executed – MName Name has been updated
Depending upon the column updated, a message will be displayed. With this feature we can determine which column in the table has been updated, and then proceed with the business rules to be implemented further.
Columns_Update() function returns a varbinary data type representation of the columns updated. This function return a hexadecimal values from which we can determine which columns in the table have been updated.
INSTEAD OF Triggers
A trigger which gets executed automatically in place of triggering actions i.e., INSERT, DELETE and UPDATE is called an INSTEAD OF trigger.
INSTEAD OF triggers gets executed automatically before the Primary Key and the Foreign Key constraints are checked, whereas the traditional AFTER triggers gets executed automatically after these constraints are checked.
CREATE TRIGGER trgAfterInsert
On User_Details
FOR INSERT
AS
BEGIN
Print (‘AFTER Trigger [trgAfterInsert] – Trigger executed !!’)
END
INSERT INTO USER_DETAILS(USERID, FNAME,LNAME, MNAME, EMAIL)
VALUES(100, ‘FName’,’LName’,’MName’,’test@test.com’)