The orm framework will eventually be translated into sql execution, so it is also applicable to triggers

Trigger is a special type of stored procedure, which is different from the stored procedure we introduced earlier. Triggers are triggered by events and are automatically called and executed. The stored procedure can be called by the name of the stored procedure.

Ø what is a trigger

Trigger is a special stored procedure that will be automatically executed when inserting, updating and deleting a table. Triggers are generally used in check Constraints are more complex than constraints above. The difference between triggers and ordinary stored procedures is that triggers operate on a table. Such as: update,insert,delete During these operations, the system will automatically call and execute the corresponding trigger on the table. SQL Server 2005 Triggers in can be divided into two categories: DML Trigger and DDL Trigger, where DDL Triggers are fired because they affect multiple data definition language statements create,alter,drop sentence.



DML Triggers are divided into:

1, after Trigger (after trigger)

    a, insert trigger

    b, update trigger

    c, delete trigger



2, instead of Trigger (previously triggered)



among after A trigger requires only one operation to be performed insert,update,delete Then the trigger is triggered and can only be defined on the table. and instead of A trigger representation does not perform the actions it defines( insert,update,delete)It's just the execution trigger itself. It can be defined on the table instead of Triggers can also be defined on the view.



Triggers have two special tables: the insert table( instered Tables) and delete tables( deleted Table). These two tables are both logical and virtual. There are two tables created by the system in memory and will not be stored in the database. Moreover, the of the two tables are read-only and can only read data rather than modify data. The results of these two tables are always the same as the structure of the table applied by the changed trigger. When the trigger completes its work, the two tables will be deleted. Inserted Table data is inserted or modified data, and deleted The data of the table is the data before updating or deleting.

Operations on tables	Inserted logic table	Deleted Logical table
Add record (insert)	Store added records	nothing
delete record	nothing	Store deleted records
Modify record (update)	Store updated records	Store records before updating

When updating data, first delete the table record, and then add a record. In this way, there will be updated data records in both the inserted and deleted tables. Note: the trigger itself is a transaction, so you can perform some special checks on the modified data in the trigger. If not, you can use transaction rollback to undo the operation.

Ø create trigger

grammar

create trigger tgr_name
on table_name
with encrypion –encrypted trigger 
    for update...
as
    Transact-SQL

Create insert type trigger

--establish insert Insert type trigger
if (object_id('tgr_classes_insert', 'tr') is not null)
    drop trigger tgr_classes_insert
go
create trigger tgr_classes_insert
on classes
    for insert --Insert trigger
as
    --Define variables
    declare @id int, @name varchar(20), @temp int;
    --stay inserted Record information has been inserted into the query 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 'Student added successfully!';
go
--insert data
insert into classes values('5 class', getDate());
--Query data
select * from classes;
select * from student order by id;

insert trigger, a newly inserted record will be added 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 'Backup data';    
    if (object_id('classesBackup', 'U') is not null)
        --existence classesBackup，Insert data directly
        insert into classesBackup select name, createDate from deleted;
    else
        --non-existent classesBackup Create and then insert
        select * into classesBackup from deleted;
    print 'Data backup succeeded!';
go
--
--The number of affected rows is not displayed
--set nocount on;
delete classes where name = '5 class';
--Query data
select * from classes;
select * from classesBackup;

When deleting data, the delete trigger will save the data just deleted in the deleted table.

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 @newName = name from inserted;
            update student set name = replace(name, @oldName, @newName) where name like '%'+ @oldName + '%';
            print 'Cascade data modification succeeded!';
        end
    else
        print 'No modification required student Watch!';
go
--Query data
select * from student order by id;
select * from classes;
update classes set name = 'the fifth class' where name = '5 class';

After updating the data, the update trigger will save the data before updating in the deleted table, and the updated data in the inserted table.

Update update column level 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: class creation time cannot be modified!', 16, 11);
        rollback tran;
    end
go
--test
select * from student order by id;
select * from classes;
update classes set createDate = getDate() where id = 3;
update classes set name = 'Class four' where id = 7;

Update column level triggers can use update to determine whether to update column records;

instead of type trigger

Insert of trigger means that it does not perform the defined operations (insert, update, delete) but only the contents of the trigger itself.

Create syntax

create trigger tgr_name
on table_name
with encryption
    instead of update...
as
    T-SQL

Create instead of trigger

if (object_id('tgr_classes_inteadOf', 'TR') is not null)
    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 the deleted information and assign a value
    select @id = id, @name = name from deleted;
    print 'id: ' + convert(varchar, @id) + ', name: ' + @name;
    --Delete first student Information about
    delete student where cid = @id;
    --Delete again classes Information about
    delete classes where id = @id;
    print 'delete[ id: ' + convert(varchar, @id) + ', name: ' + @name + ' ] Your message is successful!';
go
--test
select * from student order by id;
select * from classes;
delete classes where id = 7;

Display custom message raiserror

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

Update trigger

alter trigger tgr_message
on student
after delete
as raisError('tgr_message Trigger triggered', 16, 10);
go
--test
delete from student where name = 'lucy';

Enable and disable triggers

--Disable trigger
disable trigger tgr_message on student;
--Enable trigger
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 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 create trigger statement
exec sp_helptext 'tgr_message';

Example, verifying 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,
            @name varchar(20);
    select @name = s.name, @age = s.age from inserted s;
    if (@age < 18)
    begin
        raisError('To insert new data age something the matter', 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;