记一次SQL Server Insert触发器编写过程

实现功能：新增特定类型的新闻时，自动追加特定的背景图片。

第一版(错误信息：不能在 'inserted' 表和 'deleted' 表中使用 text、ntext 或 image 列)，代码如下：

--创建insert插入类型触发器
if (object_id('tgr_news_QA_insert', 'tr') is not null)
    drop trigger tgr_news_QA_insert
go

create trigger tgr_news_QA_insert
on news
     for insert --插入触发
as
    --定义变量
    declare @id bigint, @content nvarchar(max),@newstype bigint;

    --在inserted表中查询已经插入记录信息
    select @id = id, 
    @content = [content],
    @newstype = newstype 
    from inserted

    --处理问题解答数据
    if @newstype=101
    begin
        set @content = '<div style="background:url(''images/newsBackgroundImg.jpg'') no-repeat;width:700px;height:600px;  "><div width=''100%'' style=''text-align:left;''>'+@content+'</div></div>'
        update news set content=@content where id=@id
    end

go

于是改成instead of insert触发器，代码如下：

--创建insert插入类型触发器
if (object_id('tgr_news_QA_insert', 'tr') is not null)
    drop trigger tgr_news_QA_insert
go

create trigger tgr_news_QA_insert
on news
     instead of insert --插入触发
as
    --定义变量
    declare @id bigint, @content nvarchar(max),@newstype bigint;

    --在inserted表中查询已经插入记录信息
    select @id = id, 
    @content = [content],
    @newstype = newstype 
    from inserted

    --处理对应类型的数据
    if @newstype=101
    begin
        set @content = '<div style="background:url(''images/newsBackgroundImg.jpg'') no-repeat;width:700px;height:600px;  "><div width=''100%'' style=''text-align:left;''>'+@content+'</div></div>'
        insert into news 
        select id, newstype, title, @content content, 
        from inserted
    end
    else
        insert into news 
        select * from inserted      

以上插入触发器代码虽然没有报错，也实现了效果，但是存在漏洞。

1. ntext转nvarchar(max)是否会有数据丢失
2. inserted 数据不一定只有一条

最终版:

--创建insert插入类型触发器
if (object_id('tgr_news_QA_insert', 'tr') is not null)
    drop trigger tgr_news_QA_insert
go

create trigger tgr_news_QA_insert
on news
     instead of insert --插入触发
as
        insert into news 
        select id, 
            newstype,
            title, 
            '<div style="background:url(''images/newsBackgroundImg.jpg'') no-repeat;width:700px;height:600px;  "><div width=''100%'' style=''text-align:left;''>'+convert(nvarchar(max),content)+'</div></div>', 
        from inserted
        where newstype=101

        insert into news 
        select id, 
            newstype,
            title, 
            [content], 
        from inserted
        where newstype<>101

 

关于SQL Server 2005中NTEXT与NVARCHAR(MAX)参考：http://www.cnblogs.com/dudu/archive/2009/10/17/1585152.html