Shulou(Shulou.com)06/01 Report--

Looking through the blog, I found that there is no trigger instructions, now fill in.

There are two special tables when triggers are triggered:

inserted table: whether the data in the Inserted table is inserted or modified

deleted table: whether the data in the deleted table is pre-update or deleted

These two tables have the same structure as the table applied by the modified trigger. The stored data is the data to be operated on. When the trigger completes its work, these two tables will be deleted.

modify operation record inserted table deleted table insert record store new record... Delete record............................................ store deleted records update records store updated records store pre-updated records

Instead of and After triggers

Instead of Trigger: Used to replace the T-SQL statement that caused the trigger to execute. In addition to tables, Instead of triggers can also be used with views to extend the update operations that views can support.

After Trigger: Executes after an Insert,Update, or Deleted statement, where actions such as constraint checking occur before the After trigger is activated. After triggers can only be used with tables.

Each modification action (insert,update, delete) of a table or view can have an instead of trigger, and each modification action of a table can have multiple After triggers.

Trigger add, modify, delete, view operations

1 Add Create Syntax

create trigger trigger_name on {table_name | view_name} {for | After | Instead of } [ insert, update,delete ] as sql_statement

2 Modify syntax

alter trigger_name--similar to modifying table structure

3 Delete syntax

drop trigger trigger_name

4 View triggers

select * from sys. triggerselect * from sysobjects where xtype ='TR '---View individual triggers exec sp_helptext ' Trigger name '

Trigger Examples

--1 Create trigger, trigger add content Create TRIGGER [dbo]. [s1]ON [dbo]. [A]FOR insert as DECLARE @id VARCHAR(50) DECLARE @name varchar(50) DECLARE @nos INT SELECT @id=[ID] ,@name=a.Name,@nos=a.number FROM a AS a INSERT INTO B( IC, CName, No -- AID -- this column value is auto-generated ) VALUES(@id,@name,@nos) ---2 Delete content, trigger cascade delete Create trigger [dbo]. [tri_deletemember]on [dbo]. [Member]for deleteas declare @ID VARCHAR(150) select @ID=ID from deleted delete from MemCount where ID=@ID delete from MemMoney where @ID=@ID

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.