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