Triggers a trigger is a stored procedure that is executed when an attempt is made to modify data in a table i.e protected by the trigger. trigger is defined on the insert, update, or delete action on a table. triggers will be fired automatically. types of triggers 1)dml triggers 2)ddl triggers 3)logon triggers 1)dml triggers(data manipulation language triggers) : these triggers execute when the data is inserted, modified, or deleted. 2)ddl triggers(data definition language triggers) : these triggers execute when a table or view is created, modified or deleted. 3)logon triggers : these triggers execute stored procedures when a session is established with a logon unit. a)dml triggers: these triggers are mainly divided into 3 types 1)insert triggers 2)update triggers 3)delete triggers note: sql server is maintaining two special tables 1)inserted table : inserted table contains copies of newly inserted rows and updated operations. 2)deleted table : deleted table contains copies of deleted records and before updated records. while modifying modified data is inserted into inserted table and previous data will be inserted into deleted table. note : these tables are not physically available but there are created and droped as when triggering events occur. create trigger : syntax: create trigger
on for insert | for update | for delete as statements; ex1: create trigger insertintostutemp on student for insert as insert into studenttemp select * from inserted; ex2: create trigger insertintostdel on student for delete as insert into studentdel select * from deleted;
to view the information of trigger syntax: sp_helptext triggername; to delete trigger drop trigger triggername;