Trigger is a special kind of stored procedure, similar to other programming languages function events, SQL Server allows for the INSERT, UPDATE, DELETE to create triggers, when in the table (view) to insert, update, delete records, trigger one or a series of T-SQL statement and to do something that user defined.

A trigger is used to update table automatic, and it can send an e-mail to database administrator that notice the databases’ situation.

The advantage and disadvantage of trigger

There is some advantage and disadvantage of trigger.

The advantage of trigger

1. Pre-compiled has been optimized, more efficient. To avoid the SQL statement in the network transmission and then to explain the low efficiency.

2. The trigger has a good security. There will be no SQL statement into the problem.

The disadvantage of trigger

1. Portability is the stored procedures and triggers the greatest shortcomings.

2. Occupy too much server-side resources, create a tremendous pressure on the server

3. Can’t do DDL.

4. Flip-flop troubleshooting difficulties and the data easily lead to inconsistent, the latter maintaining inconvenient.

How to Create a Trigger

You can create a trigger like following statement.

CREATE TRIGGER trigger name

ON table name or view_name

FOR INSERT, UPDATE or DELETE

T-SQL statement

In this above code ,the trigger is a keyword to create a trigger. And the for means the trigger is an after trigger ,the word insert ,update and delete is to sign the trigger is trigger by update, insert , delete or they trigger by all of them.

For example,

CREATE TRIGGER update_test

ON TABLE tb_test

FOR UPDATE

UPDATE test1 set ss = 1 where ID=1.

And this above code just shows create a simple trigger on table tb_test.When update table tb_test,the table test1 are update too.

Sometimes we should to judge a column is update or not. Then we can create a trigger like the following code.

CREATE TRIGGER update_test1

ON TABLE tb_test

FOR UPDATE

IF UPDATE (ID)

PRINT 'The ID of tb_test is update'

The above code is limit the trigger to a field.That used to check this field’s situation.

There is a instead of trigger sometime are used to create.You can create a instead of trigger like following statement.

Create trigger trigger_name

On table_name or view_name

Instead of insert, update or delete

T-SQL statement

If a trigger on view, you can update the view instead of update the entity table.

Then you can use the instead of trigger to do it.

The following code is shows how to create a instead of trigger.

create trigger view_test

on vw_test

instead of update

begin

declare @id int

declare @name varchar(50)

set @id = (select inserted.numbers from inserted)

set @name = (select inserted.names from inserted)

update table1 set names=@name,ID=@id

end

update vw_test set names='kangyi' where numbers=1002

The virtual table of inserted and deleted

Both inserted table and deleted tables is all virtual table. The inserted table is used to an insert or update trigger, if you add a record into table, the inserted table access the add information, if you update a record of table, the inserted table accesses the update information. The deleted table accesses the delete information of a table’s records. All of them are useful. The following code is shows the inserted table.

create trigger insert_test1

on table tb_test

after insert

begin

insert into test1(ID) select inserted.ID from inserted

end

create trigger update_test1

on table tb_test

after update

begin

declare @id int

declare @name varchar(50)

set @id = select inserted.ID from inserted

set @name = select inserted.names from inserted

update test1 set names=@name where ID=@id

end

The following code shows the deleted table.

create trigger delete_test1

on table tb_test

after delete

begin

insert into delete_table(ID,Names) select deleted.ID,deleted.Names from deleted

end

Recursive Triggers

Direct Recursive Triggers

Table A on the trigger changes such as insert, delete or update table A of data, the resulting in Table A of flip-flop once again trigger a direct recursive call this state of affairs.

The following code shows a direct recursive triggers on table tb_test.

create trigger directRecursive_test

on tb_test

for update

begin

declare @id int

declare @name varchar(50)

set @id = (select inserted.numbers from inserted)

set @name = (select inserted.names from inserted)

update tb_test set names=@name,numbers=@id

end

Indirect Recursion Triggers

Table A on the table B trigger that to update table data, the resulting in Table B trigger flip-flop, and table B trigger further changes in table A of data, the resulting in Table A Trigger will trigger again, this state of affairs called indirect recursion.

The following code shows a indirect recursion triggers on table tb_test and table table1.

create trigger view_test

on table1

for update

begin

declare @id int

declare @name varchar(50)

set @id = (select inserted.numbers from inserted)

set @name = (select inserted.names from inserted)

update tb_test set names=@name,numbers=@id

end

create trigger view_test

on tb_test

for update

begin

declare @id int

declare @name varchar(50)

set @id = (select inserted.numbers from inserted)

set @name = (select inserted.names from inserted)

update table1 set names=@name,ID=@id

end

The recursive triggers may be made a deadlock. If you want to use the recursive triggers, make sure control your code correctly, or it may be affect the database efficiency.