Implementing Triggers in SQL Server 2000

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’)

Continues…

Pages: 1 2 3 4 5 6




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |