View all triggers and status under the database
SELECT a.name data table name, sysobjects.name AS trigger name, sysobjects.crdate AS creation time, sysobjects.info , sysobjects.status FROM sysobjects LEFT JOIN ( SELECT * FROM sysobjects WHERE xtype = 'U' ) AS a ON sysobjects.parent_obj = a.id WHERE sysobjects.xtype = 'TR';
–type meaning
/*
C CHECK constraints
D default value or DEFAULT constraint
F FOREIGN KEY constraints
L log
FN scalar function
IF embedded table function
P stored procedure
PK PRIMARY KEY constraint (type is K)
RF replication filter stored procedure
S system table
TF table function
TR flip-flop
U user table
UQ UNIQUE constraint (type is K)
V view
X extended stored procedure
*/
–Querying the trigger SQL based on the trigger name is as follows
exec sp_helptext trigger name
–Create trigger syntax
CREATE TRIGGER trigger_name ON table_name [WITH ENCRYPTION] FOR [DELETE, INSERT, UPDATE] AS T-SQL statement GO
WITH ENCRYPTION
represents the SQL text of the encryption trigger definition
DELETE, INSERT, UPDATE
Specifies the type of trigger
1. Create an insert type trigger
Insert trigger
--Insert a piece of data into the GradeInfo table and a record into the MyStudentInfo table. IF (object_id('tr_insert','tr') is not null) drop trigger tr_insert GO CREATE trigger tr_insert onGradeInfo after insert --insert trigger as begin --Define variables declare @GradeId int --Query inserted record information in the inserted table select @GradeId=id from INSERTED --Insert data into the MyStudentInfo table insert INTO MyStudentInfo (GradeId) VALUES (@GradeId) print 'Insertion successful!' end
Insert data
insert INTO GradeInfo VALUES (11,'C + + ')
Query data
select * from MyStudentInfo where GradeId=11
2. delete trigger
Delete the data in the MyStudentInfo table and insert it into the backup table
--Delete the data in the MyStudentInfo table and insert the backup table IF (object_id('tr_Delete','tr') is not null) drop TRIGGER tr_Delete GO CREATE trigger tr_Delete onMyStudentInfo for delete as begin print 'Backing up data...' IF (object_id('MyStudentInfo_Back','U') is not null) --Exist in the table and insert data directly insert INTO MyStudentInfo_Back SELECT * from DELETED else select * into MyStudentInfo_Back from DELETED PRINT 'Backup completed' end
Query MyStudentInfo table data before deletion
select * from MyStudentInfo
Delete data with id=9
delete FROM MyStudentInfo where Id=9
Query 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 onMyStudentInfo for update as begin --Declare variables to store the names before and after the update declare @OldName varchar(16),@NewName varchar(16) select @OldName=name from DELETED print 'Name before update:' + @OldName select @NewName=name from INSERTED print 'Updated name:' + @NewName end
Update Zhang San to “Zhang San Test”
update MyStudentInfo SET Name='Zhang San Test' where Id=1
update update column level trigger
--update update column level trigger IF (object_id('tr_update_column','tr') is not null) drop TRIGGER tr_update_column GO CREATE trigger tr_update_column onGradeInfo for update as begin IF(update(id)) begin print 'System prompt: 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 trigger means that it does not execute its defined operations (insert, update, delete) but only executes the contents of the trigger itself, and its priority is higher than 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 trigger
--Create instead of trigger /*Before inserting data into the MyStudentInfo table, first determine whether there is a corresponding class ID in the GradeInfo table. If not, the insertion is not allowed. If it exists, insert it */ IF (object_id('tr_insteadOf','tr') is not null) drop TRIGGER tr_insteadOf GO CREATE trigger tr_insteadOf onMyStudentInfo 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 'This class does not exist and cannot be inserted' rollback end end
Test 1, insert non-existent class id
insert INTO MyStudentInfo (GradeId) VALUES (15)
Test 2, insert existing class id
insert INTO MyStudentInfo (GradeId) VALUES (5)
DDL trigger
create trigger tr_DDL on database for DROP_TABLE,ALTER_TABLE as begin print 'Don't think about deleting the library! Type your code well' rollback --rollback end
Test delete table
drop TABLE MyStudentInfo
Test modified table structure
alter table MyStudentInfo alter column Name varchar(32)
delete trigger
drop trigger trigger name
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