Triggers are special stored procedures that are automatically executed when inserting, updating, and deleting tables. Triggers are generally used on constraints with more complex check constraints. The difference between triggers and ordinary stored procedures is that triggers operate on a certain table. During operations such as update, insert, and delete, the system will automatically call and execute the corresponding trigger on the table. Triggers in SQL Server 2005 can be divided into two categories: DML triggers and DDL triggers. DDL triggers affect a variety of data definition language statements and are triggered. These statements include create, alter, and drop statements.
There are three common triggers: applied to Insert, Update, and Delete events respectively.
Why should I use triggers? For example, such two tables:
Create Table Student BorrowRecord int identity(1,1), StudentID int ReturnDAte Datetime, ReturnDate --Return time
... )
Functions used There are:
1. If I change the student’s student number, I hope that his borrowing record will still be related to the student (that is, the student number in the borrowing record table will be changed at the same time);
Wait.
You can use triggers at this time. For 1, create an Update trigger:
Create Trigger truStudent
As As What to do after the event is triggered
if Update (Studentid)
Begin
Set Studentid = I.Studentid
From BORROWRECORD Br, Deleted D, Inserted I -Deleted and InsertedD = d.Studentid
End
Understand the two temporary tables in the trigger: Deleted and Inserted. Note that Deleted and Inserted respectively represent the "old record" and "new record" of the table that triggered the event.
There are two virtual tables in a database tutorial system used to store information that records changes in the table, namely:
. No records are stored
Create trigger trdStudent
On Student
As
Delete BorrowRecord br , Delted d
use using using using ’ using br.Student ID ’s In this example, we can see the key to the trigger: A. 2 temporary tables; B. Trigger mechanism
DML triggers are divided into:
1. after trigger (triggered after)
a. insert trigger
When updating data, you first delete the table record and then add a record. In this way, there will be updated data records in the inserted and deleted tables. Note that the trigger itself is a transaction, so some special checks can be performed on modified data in the trigger. If it is not satisfied, you can use transaction rollback to undo the operation.
Ø Create trigger
Syntax
create trigger tgr_nameon table_name
with encryption triggerfor update...
asTransact-SQL
# Create insert type trigger
- -Define variables declare @id int, @name varchar(20), @temp int;
--Query inserted record information in the inserted table
select @id = id, @name = name from inserted;
set @name = @name + convert(varchar, @id);
set @temp = @id / 2;
insert into student values(@name, 18 + @id, @temp, @id);
print 'Add student successfully! ';
go
--Insert data
insert into classes values('5class', getDate());
--Query data
select * from classes;
select * from student order by id;
insert trigger , will add a newly inserted record to the inserted table.
# Create delete type trigger
--delete delete type trigger
if (object_id('tgr_classes_delete', 'TR') is not null)
go
create trigger tgr_classes_deleteon classes
for delete --Delete triggeras
print 'In backup data...'; if (object_id('classesBackup', 'U') is not null)
--ClassesBackup exists, insert data directly
insert into classesBackup select name , createDate from deleted;
else
--No classesBackup is created and then inserted
select * into classesBackup from deleted;
print 'Backup data successful! ';
go
--
--Do not display the number of affected rows
--set nocount on;
delete classes where name = '5 classes';
--Query data
select * from classes;
select * from classesBackup;
The delete trigger will save the just deleted data in the deleted table when deleting data.
# Create update type trigger
--update update type trigger
if (object_id('tgr_classes_update', 'TR') is not null)
go
create trigger tgr_classes_updateon classes
for updateas
declare @oldName varchar(20), @newName varchar(20); --data before update
select @oldName = name from deleted;
if (exists (select * from student where name like '% ' + @OLDNAME +'%'))
Begin
-updated data
select
Select
Select
Select @newname = name from inserted; e) Where name like '%' + @oldName + '%';
print 'Cascade modification of data successful! ';
END
Else
Print' does not need to modify the Student table! ';
go
--Query data
select * from student order by id;
select * from classes;
update classes set name = 'Class 5' where name = 'Class 5';
The update trigger will be updated After the data is retrieved, the data before the update is saved in the inserted table, and the updated data is saved in the inserted table.
# update update column trigger
drop trigger tgr_classes_update_column
gocreate trigger tgr_classes_update_column
on classesfor update
as -- Column level trigger: Whether the class creation time has been updated? if (update(createDate))
begin
raisError('System prompt: The class creation time cannot be modified!', 16, 11);
--Test
select * from student order by id;
select * from classes;
update classes set createDate = getDate() where id = 3;
update classes set name = 'Class 4' where id = 7;
Update Column-level triggers can use update to determine whether to update column records;
# instead of type trigger
instead of trigger means that it does not execute its defined operations (insert, update, delete) but only executes the trigger its own content.
Create grammar
create trigger tgr_name
on table_name
with encryption
instead of update...
as
T-SQL
)
drop trigger tgr_classes_inteadOfgo
create trigger tgr_classes_inteadOf
on classes
instead of delete/*, update, insert*/
as
declare @id int, @name varchar(20);
--Query deleted information, Disease assignment
select @id = id, @name = name from deleted;
print 'id: ' + convert(varchar, @id) + ', name: ' + @name;
--Delete student information first
delete student where cid = @id;
--Delete the information of classes
delete classes where id = @id;
print 'Delete [ id: ' + convert(varchar, @id) + ', name: ' + @name + ' ] message successful! ';
go
--test
select * from student order by id;
select * from classes;
delete classes where id = 7;
# Display custom message raiseerror
if (object_id('tgr_message' , 'TR') is not null)
drop trigger tgr_messagego
create trigger tgr_message
on student
after insert, update
as raisError('tgr_message trigger was triggered', 16, 10);
go
--test
insert into student values('lily', 22, 1, 7);
update student set sex = 0 where name = 'lucy';
select * from student order by id;
# Modify trigger
alter trigger tgr_message
on studentafter delete
as raisError('tgr_message trigger is triggered', 16, 10);
go
--test
delete from student where name = 'lucy';
# Enable and disable triggers
--Disable triggers
disable trigger tgr_message on student;--Enable triggers
enable trigger tgr_message on student;
# Query created trigger information
--Query existing triggers
select * from sys .triggers;select * from sys.objects where type = 'TR';
--View trigger firing events
select te.* from sys.trigger_events te join sys.triggers t
on t.object_id = te.object_id
where t.parent_class = 0 and t.name = 'tgr_valid_data';
--View the creation trigger statement
exec sp_helptext 'tgr_message';
# Example, verify inserted data
if ((object_id(' tgr_valid_data', 'TR') is not null))
drop trigger tgr_valid_datago
create trigger tgr_valid_data
on student
after insert
as
declare @age int,
select @name = s .name, @age = s.age from inserted s;
if (@age < 18)
begin
raisError('There is a problem with the age of new data inserted', 16, 1);
rollback tran;
end
go
--test
insert into student values('forest', 2, 0, 7);
insert into student values('forest', 22, 0, 7);
select * from student order by id;
# Example, operation log
drop table log
gocreate table log(
id int identity(1, 1) primary key,
action varchar(20 ),
createDate datetime default getDate()
)
go
if (exists (select * from sys.objects where name = 'tgr_student_log'))
drop trigger tgr_student_log
go
create trigger tgr_student_log
on student
after insert, update , delete
as
if ((exists (select 1 from inserted)) and (exists (select 1 from deleted)))
begin
insert into log(action) values('updated');
end
else if (exists (select 1 from inserted) and not exists (select 1 from deleted))
begin
insert into log(action) values('inserted');
end
else if (not exists (select 1 from inserted) and exists (select 1 from deleted))
begin
insert into log(action) values('deleted');
end
go
--test
insert into student values('king', 22, 1, 7);
update student set sex = 0 where name = 'king';
delete student where name = 'king';
select * from log;
select * from student order by id;