数据库触发器

修改操作           inserted表            deleted表
增加insert        存放新增的记录          -------
删除delete          ---------           存放被删除的记录
修改update        存放更新后的记录      存放更新前的记录

使用游标，删除一个表的内容，把符合条件的内容增加到另外一个表
CREATE TRIGGER tg_test0
   ON  t_stu
   AFTER delete
AS
BEGIN
    declare @name varchar(50)
    declare cur cursor
    for select sname from deleted
    open cur
    while @@fetch_status=0
       begin
          fetch next from cur into @name
          insert into t_outstu values(@name)
       end
    close cur
    deallocate cur

END
GO

更新一个表的某一项数据，把另外一个表的这个数据也更新
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER TRIGGER [dbo].[tg_teg]
   ON  [dbo].[t_card]
   AFTER update
AS
BEGIN
    declare @no int
    select @no=no from inserted--更新后的值
    declare @nn int
    select @nn=no from deleted--更新前的值
    update t_borrow set no=@no where no=@nn
END

instead of 触发器的使用（原操作语句被替换）
LTER TRIGGER [dbo].[tg_test]
   ON  [dbo].[t_card]
   instead of delete
AS
BEGIN
    declare @n int
    declare @m int
    select @n=no from deleted
    select @m=count(*) from t_borrow where no=@n
    if(@m=0)
      begin
         delete from t_card where no=@n
      end
    else
      begin
        select '存在借书记录不能删除'
      end

END


触发器

ALTER TRIGGER [dbo].[T_t1]
   ON  [dbo].[T_Class]
   AFTER  UPDATE
AS
BEGIN
    declare @oldcid int
    declare @name varchar(50)
    select @name=cname ,@oldcid=cid from deleted
    declare @nename varchar(50)
select @nename=cid from T_class where
cname= @nename
update t_stu set cid=@nename  where cid=@oldcid
    SET NOCOUNT ON;

 

END



ALTER TRIGGER [dbo].[tg_1]
   ON  [dbo].[T_Class]
   AFTER update
AS
BEGIN
    declare @n int
    declare @m int
    select @n=cid from deleted
    select @m=cid from inserted
    update T_stu set cid=@m where cid=@n
END


ALTER TRIGGER [dbo].[tg_class]
   ON  [dbo].[T_Class]
   AFTER DELETE
AS
BEGIN
    
    SET NOCOUNT ON;

   select * from t_class

END



ALTER TRIGGER [dbo].[tg_ClassID]
   ON  [dbo].[T_Class]
   AFTER UPDATE
AS
BEGIN
    declare @id int
    select @id=cid from inserted
    update t_stu set cid=@id
    where cid in
    (select cid from deleted)
    

END


ALTER TRIGGER [dbo].[tg_DelClass]
   ON  [dbo].[T_Class]
   Instead of  DELETE
AS
BEGIN
    declare @id int
    select @id=cid from deleted
    if exists(select * from t_stu where cid=@id)
    begin
        select '当前班级存在学生，不能删除'
    end
    else
    begin
        delete from t_class where cid=@id
    end

END