SQL Server trigger and SQL Server trigger

SQL Server trigger and SQL Server trigger
A trigger is a special type of stored procedure, which is different from the stored procedure we introduced earlier. A trigger is triggered by an event and automatically called for execution. The stored procedure can be called by the name of the stored procedure.

Ø what is a trigger?

A special stored procedure that is automatically executed when a trigger inserts, updates, or deletes a table. Triggers are generally used for constraints with more complex check constraints. The difference between a trigger and a common stored procedure is that a trigger is used to operate a table. For example, update, insert, and delete operations, the system automatically calls the trigger for executing the table. In SQL Server 2005, triggers can be divided into two types: DML triggers and DDL triggers. DDL triggers affect the execution of multiple data definition language statements, including create, alter, and drop statements.

 

DML triggers are divided:

1. after trigger (triggered later)

A. insert trigger

B. update trigger

C. delete trigger

 

2. instead of trigger (previously triggered)

 

The after trigger requires that the trigger be triggered only after an insert, update, or delete operation is executed and can only be defined on the table. The instead of trigger does not execute the defined operations (insert, update, delete) but only executes the trigger itself. You can either define an instead of trigger on a table or a view.

 

The trigger has two special tables: The Insert table and the delete table ). These two tables are logical tables and virtual tables. Two tables created by the system in the memory are not stored in the database. Both tables are read-only and can only read data but cannot modify data. The results of these two tables are always the same as the structure of the table applied by the modified trigger. After the trigger is complete, the two tables will be deleted. The data in the Inserted Table is the Inserted or modified data, and the data in the deleted table is the data before or after the update.

 

Table operations	Inserted logical table	Deleted logical table
Insert)	Store added records	None
Delete)	None	Store deleted records
Update)	Store updated records	Store records before update

When updating data, you delete the table record and add a record. In this way, the inserted and deleted tables have updated data records. 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 cancel the operation.

 

Create a trigger

Syntax

Create trigger tgr_nameon table_namewith encrypion-encryption trigger for update... as Transact-SQL

# Create an insert trigger

-- Create the insert trigger if (object_id ('tgr _ classes_insert ', 'tr') is not null) drop trigger tgr_classes_insertgocreate trigger tgr_classes_inserton classes for insert -- insert trigger as -- Define the variable declare @ id int, @ name varchar (20), @ temp int; -- query the inserted records 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 'student added successfully! '; Go -- insert data into classes values ('5ban', getDate (); -- query data select * from classes; select * from student order by id;

Insert trigger. A newly inserted record is added to the inserted Table.

 

# Create a delete trigger

-- Delete: if (object_id ('tgr _ classes_delete ', 'tr') is not null) drop trigger tgr_classes_deletegocreate trigger tgr_classes_deleteon classes for delete -- delete trigger as print in backup data ...... '; If (object_id ('classesbackup', 'U') is not null) -- classesBackup exists, insert data directly into classesBackup select name, createDate from deleted; else -- no classesBackup is created, and then select * into classesBackup from deleted; print 'is successfully inserted! '; Go ---- the number of affected rows is not displayed -- set nocount on; delete classes where name = '5ban'; -- query data select * from classes; select * from classesBackup;

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

 

# Create an update trigger

-- Update trigger if (object_id ('tgr _ classes_updat', 'tr') is not null) drop trigger triggers trigger tgr_classes_updateon classes for updateas declare @ oldName varchar (20 ), @ newName varchar (20); -- select @ oldName = name from deleted; if (exists (select * from student where name like '%' + @ oldName + '%') begin -- select @ newName = name from inserted; update student set Name = replace (name, @ oldName, @ newName) where name like '%' + @ oldName + '%'; print 'cascade data modification successful! '; End else print' no need to modify the student table! '; Go -- query data select * from student order by id; select * from classes; update classes set name = 'five class' where name = 'five class ';

After the update trigger updates the data, it saves the pre-update data in the deleted table and the updated data in the inserted Table.

 

# Update a column-Level Trigger

If (object_id ('tgr _ classes_update_column ', 'tr') is not null) drop trigger tgr_classes_update_columngocreate trigger tgr_classes_update_columnon classes for updateas -- column-level triggers: whether the class creation time is updated. if (update (createDate) begin raisError ('system prompt: the class creation time cannot be modified! ', 16, 11); rollback tran; endgo -- Test select * from student order by id; select * from classes; update classes set createDate = getDate () where id = 3; update classes set name = 'four class' where id = 7;

You can use update to determine whether to update Column records;

 

# Instead of Type trigger

The instead of trigger indicates that the operation defined by the trigger is not executed (insert, update, delete), but only the content of the trigger itself.

Create syntax

create trigger tgr_nameon table_namewith encryption    instead of update...as    T-SQL

# Create an instead of trigger

If (object_id ('tgr _ classes_inteadOf ', 'tr') is not null) drop trigger tgr_classes_inteadOfgocreate trigger tgr_classes_inteadOfon classes instead of delete/*, update, insert */as declare @ id int, @ name varchar (20); -- query the information to be deleted, assign the select @ id = id, @ name = name from deleted; print 'id: '+ convert (varchar, @ id) +', name: '+ @ name; -- delete the student information first. delete student where cid = @ id; -- delete the classes Information delet. E classes where id = @ id; print 'the message [id:' + convert (varchar, @ id) + ', name:' + @ name + '] is deleted successfully! '; Go -- testselect * 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_messagegocreate trigger tgr_messageon student after insert, updateas raisError ('tgr _ message trigger triggered ', 16, 10); go -- testinsert into student values ('lily', 22, 1, 7); update student set sex = 0 where name = 'Lucy '; select * from student order by id;

# Modify a trigger

Alter trigger tgr_messageon studentafter deleteas raisError ('tgr _ message trigger triggered ', 16, 10); go -- testdelete from student where name = 'Lucy ';

# Enable or disable a trigger

-- Disable the trigger disable trigger tgr_message on student; -- enable the trigger enable trigger tgr_message on student;

# Querying created trigger information

-- Query the existing trigger select * from sys. triggers; select * from sys. objects where type = 'tr'; -- view the trigger event select te. * from sys. trigger_events te join sys. triggers ton t. object_id = te. object_idwhere t. parent_class = 0 and t. name = 'tgr _ valid_data '; -- view the create trigger statement exec sp_helptext 'tgr _ message ';

# Example: Verify the inserted data

If (object_id ('tgr _ valid_data ', 'tr') is not null) drop trigger triggers tgr_valid_dataon studentafter insertas declare @ age int, @ name varchar (20 ); select @ name = s. name, @ age = s. age from inserted s; if (@ age <18) begin raisError ('Age when new data is inserted has a problem ', 16, 1); rollback tran; endgo -- testinsert 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 loggocreate table log(    id int identity(1, 1) primary key,    action varchar(20),    createDate datetime default getDate())goif (exists (select * from sys.objects where name = 'tgr_student_log'))    drop trigger tgr_student_loggocreate trigger tgr_student_logon studentafter insert, update, deleteas    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');    endgo--testinsert 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;

In the SQL server trigger, update insert delete is used to give the write example

INSERT:
Table 1 (ID, NAME) Table 2 (ID, NAME) after you insert Table 1 data, table 2 is also inserted with the same data. create trigger TRI1ON table 1FOR insertasbegin insert into Table 2 SELECT * FROM INSERTEDENDGO
DELETE:
Table 1 (ID, NAME) Table 2 (ID, AGE, GENDER) after you delete the data of an ID in Table 1, table 2's corresponding ID data is also deleted create trigger TRI2 ON table 1FOR deleteasbegin declare @ id INTSELECT @ id from deleted delete table 2 where id = @ idENDGO
UPDATE:
Table 1 (ID, NAME) Table 2 (ID, NAME) after you change the data in table 1 NAME column, table 2's corresponding ID data also updates create trigger TRI3ON table 1FOR updateasif update (NAME) begin update Table 2 set. NAME = B. name from table 2 A, inserted B WHERE. ID = B. IDENDGO

SQL Server trigger Problems

Careate trigger trigger_name on B
For insert
As
Declare @ a varchar (20 ),
Seelct @ a = name from inserted // Note: inserted and deleted are special tables of SQLServer triggers.
If not exists (select name from A where name = @)
Begin
Rollback transaction
Else
Commit
End
Go