Testing DML Trigger in SQL Server

Question

I want to test a DML Trigger in SQL Server. So what can be the method to do it

Priya Linge · Answer

Hi, DML Triggers Testing for UPDATE or INSERT Actions to Specific Columns : You can design a Transact-SQL trigger to perform certain actions based on UPDATE or INSERT modifications to specific columns. Use UPDATE() or COLUMNS_UPDATED in the body of the trigger for this purpose. UPDATE() tests for UPDATE or INSERT tries on one column. COLUMNS_UPDATED tests for UPDATE or INSERT actions that are performed on multiple columns and returns a bit pattern that indicates which columns were inserted or updated. Example> A. Using an IF UPDATE() clause to test data modifications CREATE TABLE my_table* (a int NULL, b int NULL) GO CREATE TRIGGER my_trig ON my_table FOR INSERT AS IF UPDATE(b) PRINT 'Column b Modified' GO B. Using the COLUMNS UPDATED function to test data modifications CREATE TRIGGER my_trig2 ON my_table FOR INSERT AS IF ( COLUMNS_UPDATED() & 2 = 2 ) PRINT 'Column b Modified' GO Please check below link for more information. http://technet.microsoft.com/en-us/library/ms189799.aspx http://msdn.microsoft.com/en-us/library/ms190227(v=sql.90).aspx Hope this will help you. Thanks.

Testing DML Trigger in SQL Server

Answers (1)