Use of sql server triggers

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, UPDATESpecifies 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