Wednesday , September 19 2018
Home / DataBase / SQL SERVER / step by step Procedure for Creating DML Triggers

step by step Procedure for Creating DML Triggers

In Previous Article we seen types of Triggers in SQL Server , now in this article we will know steps for how to create DML triggers on a table.In this example we are creating trigger for employee table

1. After triggers or For triggers:

Step 1: Create a sample table employee Like below screen


trigger_step1

Step 2: Create a trigger on employee table for restrict insertions on Sundays. Please follow below screen.

trigger_step2

Step 3: If We  try to insert data into employee table on Sundays the trigger fires automatically and it won’t allow data into employee table.

trigger_step3

Step 4: employee table accepts data  except Sundays. Like system date is not Sunday employee accept data please follow below screen

trigger_step4

Step 5:  Create a trigger on employee table for restrict if we try to update join_date is Sunday. Please follow below screen.

trigger_step5

Step 6: If We  try to update join_date is  Sunday in employee table then trigger fires automatically and it won’t update data if join_date is Sunday.

trigger_step6

Step 7 : Create sample table for storing deleted records for maintain history for deleted records.

trigger_step7

Step 8: Create a trigger on employee table if we delete records in employee table  these records automatically insert into deleted_history table. Please follow below screen

trigger_step8

Step 9: Before delete operation on employee table we have records in employee and deleted_history table.Please follow below screen.

trigger_step9

Step 10: Perform delete operation on employee table.

trigger_step10

Step 11: After delete operation on employee table we have records in employee and deleted_history table.Please follow below screen

trigger_step11

2.Instead of triggers

Create a instead of  trigger on employee table for restrict DML operation.Please follow below screen

trigger_step12

creating DDL Triggers step step by Step

Conclusion:

Did you find this post as useful? I hope you liked the article. Please share your valuable suggestions and feedback in the comments.

Print Friendly, PDF & Email

Give us your valuable feedback

comments

About Samba

Check Also

sql server backup script

Backup all databases in SQL Instance with one script

Backup: A copy of SQL Server data that can be used to restore and recover the …