触发器 trigger

选课表添加触发器检查是否存在学生课程分数都通过才允许插入

create trigger sc_insert_trig on StudentCourse for insert AS declare @sid uniqueidentifier,@tid uniqueidentifier, @grade int select @sid = sid, @tid = tid, @grade = grade from inserted if ( not exists (select * from Student where id = @sid) or not exists (select * from Teacher where id = @tid) or @grade not between 50 and 100 ) Rollback TRANSACTION else print 'success'

for insert 相当于 after insert

简单的trigger

CREATE TRIGGER Trig_C ON JWGL.dbo.Course
AFTER INSERT
AS
Select * from INSERTED;

INSERT INTO Course (NAME)
VALUES(N'思修')

触发器 trigger

创建触发器的时候会字段生成 INSERTED表，如果触发器中设置了AFTER INSERT，这个表就是此次插入的数据

判断当前操作是什么类型

CREATE TRIGGER Trig_C ON JWGL.dbo.Course
AFTER INSERT , UPDATE, DELETE 
AS
BEGIN
  SET  XACT_ABORT  ON -- 如果产生错误自动回滚
  DECLARE
  @IsInsert bit,
  @IsUpdate bit,
  @IsDelete bit

  IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
    SET @IsInsert = 1
  ELSE
      SET @IsInsert = 0

  IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
      SET @IsUpdate = 1
  ELSE
      SET @IsUpdate = 0

  IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
      SET @IsDelete = 1
  ELSE
      SET @IsDelete = 0 
----------------------------
  IF  @IsInsert = 1
    BEGIN 
      print('INSERT')
    END
  ELSE 
  IF  @IsUpdate = 1
    BEGIN 
      print('updatad')
    END
    ELSE 
    IF @IsDelete = 1
    BEGIN 
      print('DEL')
    END
END
--ROLLBACK   TRANSACTION 回滚

实现思路
触发器中可以获取两张表分别是 deleted 表和inserted 当然是在AFTER的情况下里面存放变更的数据
当添加数据时 inserted表有数据 deleted表空
当删除数据时 inserted表空 deleted表有数据
当更新数据时 inserted表有新数据 deleted表有旧数据
可依次来判断操作是三种情况的哪一种
参考

更新的项可以这么看（1项）

SET  XACT_ABORT  ON -- 如果产生错误自动回滚
  DECLARE @UPDATECONTAINER nvarchar(10)
  SELECT @UPDATECONTAINER = NAME FROM INSERTED
  PRINT(@UPDATECONTAINER)

更改某属性触发可以使用UPDATE(字段名)

CREATE TRIGGER Trig_C on dbo.Course 
AFTER UPDATE 
AS
BEGIN 
  SET  XACT_ABORT  ON -- 如果产生错误自动回滚

  IF  UPDATE(NAME)
  BEGIN
    PRINT('改了name字段')
    SELECT * FROM INSERTED
  END
END

删除触发器
drop trigger 名字不加引号参考

IF EXISTS  
(SELECT * FROM sys.triggers
WHERE name = 'Trig_C')
  DROP TRIGGER Trig_C
ELSE
  PRINT('0')

UPDATE Course 
SET NAME = N'ANDROID开发'
WHERE  ID = '.975018'

--delete FROM Course 
--WHERE NAME = N'数学'
--INSERT INTO Course (NAME)
--VALUES(N'数学')

CREATE TRIGGER Trig_C on dbo.Course 
AFTER UPDATE 
AS
BEGIN 
  SET  XACT_ABORT  ON -- 如果产生错误自动回滚

  IF  UPDATE(NAME)
  BEGIN
    PRINT('改了name字段')
    SELECT * FROM INSERTED
  END
END

/*
CREATE TRIGGER Trig_C ON JWGL.dbo.Course
AFTER INSERT , UPDATE, DELETE 
AS
BEGIN
  SET  XACT_ABORT  ON -- 如果产生错误自动回滚
  DECLARE
  @IsInsert bit,
  @IsUpdate bit,
  @IsDelete bit

  IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
    SET @IsInsert = 1
  ELSE
      SET @IsInsert = 0

  IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
      SET @IsUpdate = 1
  ELSE
      SET @IsUpdate = 0

  IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
      SET @IsDelete = 1
  ELSE
      SET @IsDelete = 0 
----------------------------
  IF  @IsInsert = 1
    BEGIN 
      print('INSERT')
    END
  ELSE 
  IF  @IsUpdate = 1
    BEGIN 
      print('updatad')
    END
    ELSE 
    IF @IsDelete = 1
    BEGIN 
      print('DEL')
    END
END
--  Select * from INSERTED;
--ROLLBACK   TRANSACTION 回滚
*/

判断当前操作是什么类型

相关文章