Trigger study
目录
The advantage and disadvantage of trigger
The virtual table of inserted and deleted
Trigger introduction
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
AS
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
AS
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
AS
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
As
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
as
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
as
begin
insert into test1(ID) select inserted.ID from inserted
end
create trigger update_test1
on table tb_test
after update
as
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
as
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
as
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
as
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.