SQL SERVER triggers

A trigger is a special kind of stored procedure that is invoked primarily by an event to trigger, and the stored procedure must be called through the name of the stored procedure.

First, the definition of triggers

A trigger is a special stored procedure that is automatically executed when an INSERT, update, or delete operation is made on a table. Triggers are typically used to enforce business rules, which are advanced constraints that define more complex constraints than CHECK constraints: You can execute complex SQL statements (If/while/case), and you can reference columns in other tables. A trigger is defined on a particular table, related to a table, automatically triggered for execution, not directly called, and is a transaction (rollback).

Second, trigger classification

Triggers in SQL Server can be divided into two categories: DML triggers and DDL triggers, which are triggered against tables and DDL triggers that affect a variety of data definition language statements that have create, alter, and DROP statements.

DML triggers are divided into:

1. After trigger (trigger later)

A, insert trigger

B, UPDATE trigger

C, delete trigger

2. Instead of trigger (previously triggered)

After triggers require that a trigger be triggered only after performing an operation (insert, UPDATE, delete) and can only be defined on the table. The instead of trigger indicates that it does not perform its defined operations (INSERT, UPDATE, delete), but only the execution of the trigger itself, which takes precedence over the execution of the triggering statement.

Triggers have two special tables: Insert Table (instered table) and delete table (deleted table)

Both tables are logical tables and virtual tables, and when triggered, the system automatically creates the tables in memory and is not stored in the database. Both tables are read-only and are not allowed to be modified. The results of these two tables are always the same as the structure of the tables that are applied by the trigger. When the trigger finishes working, the two tables are deleted. The inserted table temporarily saves the inserted or updated row of records, and you can check from the inserted table whether the inserted data meets the business requirements, and if not, report an error message to the user and roll back the insert operation. Deleted table temporarily saves the row of records before deletion or update, you can check whether the deleted data meets the business requirements from the deleted table, and if not, report the error message to the user and roll back the delete operation. When you update the data, you delete the table records and then insert a record, and the inserted and deleted tables have updated data records.

Inserted table and deleted table storage information:

Modify Operation	Inserted table	deleted table
Add (INSERT) record	Storage of new records	No
Deleting records (delete)	No	To store deleted records
Modify (UPDATE) record	Storage of updated records	Store pre-update records

Iii. Creating triggers

Grammar:

CREATE TRIGGER trigger_name

On table_name

[WITH Encryption]

For [DELETE, INSERT, UPDATE]

As

T-SQL statements

GO

With encryption represents the SQL text of the cryptographic trigger definition

DELETE, INSERT, update specify the type of trigger

1. Create a trigger of insert type

--Insert Trigger
Insert a piece of data into the--gradeinfo table, insert a record in the Mystudentinfo table
IF (object_id (' Tr_insert ', ' tr ') is not null)
Drop Trigger Tr_insert
GO
CREATE Trigger Tr_insert
On Gradeinfo
After insert--insert Trigger
As
Begin
--Defining variables
DECLARE @GradeId int
--In the inserted table, the query has inserted record information
Select @GradeId =id from INSERTED
Inserting data into the--mystudentinfo table
Insert into Mystudentinfo (Gradeid) VALUES (@GradeId)
print ' Insert succeeded! '
End

--Inserting data
INSERT INTO Gradeinfo VALUES (one, ' C + + ')

--Querying data
SELECT * FROM Mystudentinfo where gradeid=11

2. Delete Trigger
--Delete the data from the Mystudentinfo table, insert the backup table
IF (object_id (' Tr_delete ', ' tr ') is not null)
Drop TRIGGER Tr_delete
GO
CREATE Trigger Tr_delete
On Mystudentinfo
For delete
As
Begin
print ' is backing up data ... '
IF (object_id (' Mystudentinfo_back ', ' U ') is not null)
--Existence table, inserting data directly
INSERT INTO Mystudentinfo_back SELECT * from DELETED
Else
SELECT * Into Mystudentinfo_back from DELETED
PRINT ' backup complete '
End

--Query Mystudentinfo table data before deleting
SELECT * FROM Mystudentinfo

--delete Id=9 's data
Delete from Mystudentinfo where id=9

--Querying Backup table data
SELECT * FROM Mystudentinfo_back

3. Update Trigger
IF (object_id (' tr_update ', ' tr ') is not null)
Drop TRIGGER Tr_update
GO
CREATE Trigger Tr_update
On Mystudentinfo
For update
As
Begin
--declaring variables, storing pre-and post-update names
DECLARE @OldName varchar (+), @NewName varchar (16)
Select @OldName =name from DELETED
print ' Update before name: ' [email protected]
Select @NewName =name from INSERTED
print ' Updated name: ' [email protected]
End

--Update Zhang San to "Zhang San test"
Update Mystudentinfo SET name= ' Zhang San test ' where id=1

--update Updating column-level triggers
IF (object_id (' Tr_update_column ', ' tr ') is not null)
Drop TRIGGER Tr_update_column
GO
CREATE Trigger Tr_update_column
On Gradeinfo
For update
As
Begin
IF (update (ID))
Begin
print ' system hint: Primary key ID cannot be updated '
Rollback
End
End

--Update ID column
Update Gradeinfo SET id=15 where id=4

4. Instead OF trigger

Instead of triggers indicate that the operation (INSERT, UPDATE, delete) is not executed, but only the content of the execution trigger itself, which takes precedence over the execution of the defined SQL statement

Grammar:

Create Trigger Tgr_name
On table_name
With encryption
Instead of update ...
As
Begin
T-SQL
End

--Create instead OF triggers
Before inserting data into the/*mystudentinfo table, first determine if there is a class ID in the Gradeinfo table, if not, insert it, or insert */if it exists.
IF (object_id (' tr_insteadof ', ' tr ') is not null)
Drop TRIGGER Tr_insteadof
GO
CREATE Trigger Tr_insteadof
On Mystudentinfo
Instead of insert
As
Begin
IF exists (SELECT * from Gradeinfo WHERE id= (select Gradeid from INSERTED))
print ' This class exists and can be inserted '
Else
Begin
print ' The class does not exist and cannot be inserted '
Rollback
End
End

--Test 1, insert a class ID that does not exist
Insert into Mystudentinfo (Gradeid) VALUES (15)

--Test 2, insert the existing class ID
Insert into Mystudentinfo (Gradeid) VALUES (5)

DDL triggers

Create trigger TR_DDL on database
For drop_table,alter_table
As
Begin
print ' Don't think about deleting the library! Well, hit your code.
Rollback--rollback
End

--Test Delete table
Drop TABLE Mystudentinfo

--Test Modify table structure
ALTER TABLE Mystudentinfo
ALTER COLUMN Name varchar (32)

--Disable DML triggers
Disable Trigger tr_insteadof on Mystudentinfo

--Enable DML triggers
Enable trigger Tr_insteadof on Mystudentinfo

--Disable DDL triggers
Disable trigger TR_DDL on database

--Enable DDL triggers
Enable trigger TR_DDL on database

SQL SERVER triggers