SqlServer中如何写触发器,调用Com发送电子邮件等等

原文:http://msdn.microsoft.com/msdnmag/issues/04/04/DataPoints/default.aspx

这篇FQA概述了写T-Sql语句时经常碰到的问题,还有用SqlServer发送电子邮件的源码下载,一个字"牛".

#"关于触发器的判断语句该怎么写?"小弟很欣赏这个话题.

#"第二个话题是Sql语句如果调用Com组件?"其中发送电子邮件的源码实在太好了.小弟在里贴出来方便
自己以后使用.

ALTER PROCEDURE prSendMail
    @sTo VARCHAR(500), 
    @sBody VARCHAR(8000), 
    @sSubject VARCHAR(500),
    @sFrom VARCHAR(500)
AS

    DECLARE @nCDO INT, 
        @nOLEResult INT, 
        @nOutput INT, 
        @sSource VARCHAR(255), 
        @sDescription VARCHAR(255),
        @sProgID VARCHAR(50),
        @sMsg VARCHAR(250)

    SET @sProgID = 'CDO.Message'

    EXECUTE @nOLEResult = sp_OACreate @sProgID, @nCDO OUT
    IF @nOLEResult <> 0 
    BEGIN
        EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT
        SET @sMsg = 'Error creating object ' + @sProgID + 
            '. Error # = ' + CAST(@nOLEResult AS VARCHAR(20)) + 
            '. Description = ' + @sDescription
        RAISERROR (@sMsg, 16, 1)
        RETURN
    END
    
    EXECUTE @nOLEResult = sp_OASetProperty @nCDO, 'To', @sTo
    IF @nOLEResult <> 0 
    BEGIN
        EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT
        SET @sMsg = 'Error executing CDO.To property. Error # = ' + 
            CAST(@nOLEResult AS VARCHAR(20)) + 
            '. Description = ' + @sDescription
        RAISERROR (@sMsg, 16, 1)
        RETURN
    END

    EXECUTE @nOLEResult = sp_OASetProperty @nCDO, 'From', @sFrom
    IF @nOLEResult <> 0 
    BEGIN
        EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT
        SET @sMsg = 'Error executing CDO.From property. Error # = ' + 
            CAST(@nOLEResult AS VARCHAR(20)) + 
            '. Description = ' + @sDescription
        RAISERROR (@sMsg, 16, 1)
        RETURN
    END

    EXECUTE @nOLEResult = sp_OASetProperty @nCDO, 'Subject', @sSubject
    IF @nOLEResult <> 0 
    BEGIN
        EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT
        SET @sMsg = 'Error executing CDO.Subject property. Error # = ' + 
            CAST(@nOLEResult AS VARCHAR(20)) + 
            '. Description = ' + @sDescription
        RAISERROR (@sMsg, 16, 1)
        RETURN
    END

    EXECUTE @nOLEResult = sp_OASetProperty @nCDO, 'TextBody', @sBody
    IF @nOLEResult <> 0 
    BEGIN
        EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT
        SET @sMsg = 'Error executing CDO.TextBody property. Error # = ' + 
            CAST(@nOLEResult AS VARCHAR(20)) + 
            '. Description = ' + @sDescription
        RAISERROR (@sMsg, 16, 1)
        RETURN
    END

    —Call Send method of the object
    EXECUTE @nOLEResult = sp_OAMethod @nCDO, 'Send', Null
    IF @nOLEResult <> 0 
    BEGIN
        EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT
        SET @sMsg = 'Error executing CDO.Send method . Error # = ' + 
            CAST(@nOLEResult AS VARCHAR(20)) + 
            '. Description = ' + @sDescription
        RAISERROR (@sMsg, 16, 1)
        RETURN
    END
    
    —Destroy CDO
    EXECUTE @nOLEResult = sp_OADestroy @nCDO

    RETURN @nOLEResult

GO