Detailed explanation of sql trigger operation

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);

2. If the student has graduated, I I hope to delete his student ID and also delete his book borrowing records.

Wait.

You can use triggers at this time. For 1, create an Update trigger:



Create Trigger truStudent

Create Trigger Event Trigger

As As What to do after the event is triggered
if Update (Studentid)
Begin

Update BORROWRECORD

Set Studentid = I.Studentid

From BORROWRECORD Br, Deleted D, Inserted I -Deleted and Inserted

D = 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

Modify When the new record is used for updating, the record before the update is stored. When the record is deleted, the record is not stored. The deleted record is stored. An Update process can be viewed as: generating new records to the Inserted table and copying old records to the Deleted table. , then delete the Student record and write a new record.

For 2, create a Delete trigger

Create trigger trdStudent
On Student

for Delete

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

At B. Update trigger

C, delete trigger

2, Instead of trigger (previous trigger)

Among them, the AFTER trigger requires that only a certain operation of Insert, Update, Delete Triggered and can only be defined on tables. Instead of a trigger, it means that it does not execute its defined operations (insert, update, delete) but only executes the trigger itself. Instead of triggers, you can define them on the table or on the view.

There are two special tables for triggers: insert table (instered table) and delete table (deleted table). These two are logical tables and virtual tables. The system creates two tables in memory and will not store them in the database. Moreover, both tables are read-only, and data can only be read but not modified. The results of these two tables always have the same structure as the table to which the modified trigger applies. When the trigger completes its work, the two tables are deleted. The data in the Inserted table is the data after insertion or modification, while the data in the deleted table is the data before updating or deletion.

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 trigger

for update...

as

Transact-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)

drop trigger tgr_classes_delete

go

create trigger tgr_classes_delete

on classes

for delete --Delete trigger

as

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)

drop trigger tgr_classes_update

go

create trigger tgr_classes_update

on classes

for update

as

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

if (object_id('tgr_classes_update_column', 'TR') is not null)

drop trigger tgr_classes_update_column

go

create trigger tgr_classes_update_column

on classes

for 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_inteadOf

go
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_message

go
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 student

after 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_data

go
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

if (object_id('log', 'U') is not null)

drop table log

go

create 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;