ACCESS数据库升级到SQL数据库(三)触发器的使用

# 创建insert类型触发器

--创建insert插入类型触发器
if (object_id('tgr_classes_insert', 'tr') is not null)
    drop trigger tgr_classes_insert
go
create trigger tgr_classes_insert
on classes
    for insert --插入触发
as
    --定义变量
    declare @id int, @name varchar(20), @temp int;
    --在inserted表中查询已经插入记录信息
    select @id = id, @name = name from inserted;
    set @name = @name + convert(varchar, @id);
    set @temp = @id / 2;    
    insert into student values(@name, 18 + @id, @temp, @id);
    print '添加学生成功！';
go
 

    # 创建delete类型触发器

if (object_id('tgr_classes_delete', 'TR') is not null)
    drop trigger tgr_classes_delete
go
create trigger tgr_classes_delete
on classes
    for delete --删除触发
as
    print '备份数据中……';    
    if (object_id('classesBackup', 'U') is not null)
        --存在classesBackup，直接插入数据
        insert into classesBackup select name, createDate from deleted;
    else
        --不存在classesBackup创建再插入
        select * into classesBackup from deleted;
    print '备份数据成功！';
go
--
--不显示影响行数
--set nocount on;
delete classes where name = '5班';
--查询数据
select * from classes;
select * from classesBackup; 

    # 创建update类型触发器

if (object_id('tgr_classes_update', 'TR') is not null)
    drop trigger tgr_classes_update
go
create trigger tgr_classes_update
on classes
    for update
as
    declare @oldName varchar(20), @newName varchar(20);
    --更新前的数据
    select @oldName = name from deleted;
    if (exists (select * from student where name like '%'+ @oldName + '%'))
        begin
            --更新后的数据
            select @newName = name from inserted;
            update student set name = replace(name, @oldName, @newName) where name like '%'+ @oldName + '%';
            print '级联修改数据成功！';
        end
    else
        print '无需修改student表！';
go
 

    # update更新列级触发器

if (object_id('tgr_classes_update_column', 'TR') is not null)

    drop trigger tgr_classes_update_column

go

create trigger tgr_classes_update_column

on classes

    for update

as

    --列级触发器：是否更新了班级创建时间

    if (update(createDate))

    begin

        raisError('系统提示：班级创建时间不能修改！', 16, 11);

        rollback tran;

    end

go

# 修改触发器

alter trigger tgr_message

on student

after delete

as raisError('tgr_message触发器被触发', 16, 10);

go

--test

delete from student where name = 'lucy';

    # 启用、禁用触发器

--禁用触发器

disable trigger tgr_message on student;

--启用触发器

enable trigger tgr_message on student;

    # 查询创建的触发器信息

--查询已存在的触发器
select * from sys.triggers;
select * from sys.objects where type = 'TR';

--查看触发器触发事件
select te.* from sys.trigger_events te join sys.triggers t
on t.object_id = te.object_id
where t.parent_class = 0 and t.name = 'tgr_valid_data';

--查看创建触发器语句
exec sp_helptext 'tgr_message';

实例

ALTER trigger [dbo].[update_task] on [dbo].[编程信息]
for delete,insert,update
as    
    declare @order nvarchar(20),@name nvarchar(25);    
    declare @mycursor cursor;

    /**删除时执行**/ 
    set @mycursor = cursor for select 令号,编程 from deleted  where 状态<>'已完成';
    open @mycursor;
    fetch next from @mycursor into @order,@name;
    while(@@fetch_status=0)
    begin        
        update 编程人员 set 未完成=未完成-1 where 姓名=@name;
        update 模具信息 set 未完成=未完成-1 where 令号=@order;        
        fetch next from @mycursor into @order,@name;
    end
    close @mycursor
    
    /**插入时执行**/ 
    set @mycursor=cursor for select 令号,编程 from inserted where 状态<>'已完成';
    open @mycursor
    fetch next from @mycursor into @order,@name;
    while(@@fetch_status=0)
    begin        
        update 编程人员 set 未完成=未完成+1 where 姓名=@name;
        update 模具信息 set 未完成=未完成+1 where 令号=@order;        
        fetch next from @mycursor into @order,@name;
    end
    close @mycursor
    deallocate @mycursor