Saturday , July 21 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

Find all rows count and columns count in single database in sql server

In General for any database it contains lot of tables, if we want to find …