sqlserver2000中触发器小实例！

最新推荐文章于 2018-05-18 18:23:52 发布

原创最新推荐文章于 2018-05-18 18:23:52 发布 · 3.5k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sqlserver #insert #delete

sqlserver 专栏收录该内容

5 篇文章

订阅专栏

本文介绍了SQL触发器的应用实例，包括insert、update及delete三种类型的触发器实现方式，演示了如何同步更新索引表来保持数据一致性。

--insert 触发器

create trigger tri_infoDetails_i on info_details
after insert
as
    declare @id int
begin
    --delete from info_details where id=
    select @id=id from inserted;
    insert into info_details_index(TYPE,TITLE,content,POST_TIME,FLAG)
    select type,title,content,getdate(),1 from info_details where id=@id;
    --update info_details_index set content=content
end;

-- update触发器

--select top 0 type,title,content,getdate() as post_time,1 as flag into info_details_index from info_details;
create trigger tri_infoDetails_u on info_details
after update
as
    declare @id int
begin
    if exists(select 1 from inserted)
         if exists(select 1 from deleted)
         begin
        select @id=id from inserted;
        insert into info_details_index(TYPE,TITLE,content,POST_TIME,FLAG)select type,title,content,getdate(),-1 from info_details where id=@id;
        insert into info_details_index(TYPE,TITLE,content,POST_TIME,FLAG)select type,title,content,getdate(),1 from info_details where id=@id;
         end

    --update info_details_index set content=content
end

--delete触发器

create trigger tri_infoDetails_d on info_details
after delete
as
    declare @id int
begin

         if exists(select 1 from deleted)
         begin

        insert into info_details_index(TYPE,TITLE, POST_TIME,FLAG)
        select type,title, getdate(),-1 from deleted info_details ;
         end


end