数据库触发器获取触发触发器的sql语句 -PHP开发-PHP中文网

首页

php教程

PHP开发

数据库触发器获取触发触发器的sql语句

高洛峰

Dec 14, 2016 pm 04:33 PM

sql触发器

--触发器

ALTER          TRIGGER   TR_StandardLog   ON   T_Standard

FOR   INSERT,DELETE,UPDATE

AS

--ROWCNT

DECLARE   @ROWS   INT

SET   @ROWS=@@ROWCOUNT

SET   NOCOUNT   ON

--SQL 存储执行的sql语句

CREATE   TABLE   #T   (

EVENTTYPE    VARCHAR(20),

PARAMETERS   INT,

EVENTINFO   VARCHAR(300)

)

--获取sql语句
DECLARE @SPID VARCHAR(20)

SET @SPID=CAST(@@SPID AS VARCHAR)

--OPTYPE

DECLARE   @OPTYPE   INT

SET   @OPTYPE=2                                                                                                                                 --UPDATE

IF   NOT   EXISTS   (SELECT   1   FROM   INSERTED)   SET   @OPTYPE=3                         --DELETE

IF   NOT   EXISTS   (SELECT   1   FROM   DELETED)   SET   @OPTYPE=1                          --INSERT

IF @OPTYPE=1
    BEGIN
        INSERT INTO T_StandardLog(SqlText,OPTYPE,CreateTime)
        SELECT 'INSERT INTO TBWEIGH(WEIGHID,FID,CARDNO,STDNNO,PRONAME,PROMODEL,UNIT_FROM,UNIT_TO,TRANSPORT,DRIVER,'
            +'PACKAGE,GROSS_WEIGHT,TARE_WEIGHT,NET_WEIGHT,DEDUCT,FACT_WEIGHT,PRICE,AMOUNT,UNIT,GROSS_TIME,'
            +'TARE_TIME,NET_TIME,EDIT_TIME,OPMAN_GROSS,OPMAN_TARE,OPMAN,EDITMAN,SCALENAME_GROSS,SCALENAME_TARE,'
            +'SCALENAME_NET,TRANSFER_DATE,TRANSFER_USER,REMARK,ISCANCELED,CREATETIME)'
            +'VALUES(:WEIGHID,'''+isnull(cast(A.F_ID as nvarchar(18)),'null') +''','''+isnull(A.F_CardNo,'null')+''','''+isnull(A.F_StdNo,'null')+''','''+isnull(A.F_ProName,'null')+''','''+isnull(A.F_ProModel,'null')+''','''+isnull(A.F_unitFrom,'null')+''','''+isnull(A.F_UnitTo,'null')+''','''
            +isnull(A.F_Transport,'null')+''','''+isnull(A.F_Driver,'null')+''','''+isnull(A.F_Memo,'null')+''','+isnull(cast(A.F_Gross as nvarchar(30)),'null')+','+isnull(cast(A.F_Tare as nvarchar(30)),'null')+','
            +isnull(cast(A.F_Net as nvarchar(30)),'null')+','+isnull(cast(A.F_Deduct as nvarchar(30)),'null')+','+isnull(cast(A.F_Fact as nvarchar(30)),'null')+','
            +isnull(cast(A.F_Price as nvarchar(30)),'null')+','+isnull(cast(A.F_Money as nvarchar(30)),'null')+','''+isnull(A.F_Unit,'null')+''','''+isnull(cast(A.F_GrossTime as nvarchar(30)),'null')+''','''
            +isnull(cast(A.F_TareTime as nvarchar(30)),'null')+''','''+isnull(cast(A.F_NetTime as nvarchar(30)),'null')+''','''+isnull(cast(A.F_edTime as nvarchar(30)),'null')+''','''
            +isnull(A.F_opManGross,'null')+''','''+isnull(A.F_opManTare,'null')+''','''+isnull(A.F_opMan,'null')+''','''+isnull(cast(A.F_edTime as nvarchar(30)),'null')+''','''+isnull(A.F_ScaleNameGross,'null')+''','''+isnull(A.F_ScaleNameTare,'null')+''','''
            +isnull(A.F_ScaleNameNet,'null')+''',sysdate,:UserID,:Remark,'+isnull(cast(A.F_IsCancel as nvarchar(1)),'null')+',:CREATETIME'
            +');','INSERT',GETDATE()
       FROM INSERTED A
    END
ELSE IF @OPTYPE=3 OR @OPTYPE=2
    BEGIN
       INSERT #T EXEC('DBCC INPUTBUFFER ('+@SPID+')')
       INSERT INTO T_StandardLog(SqlText,OPTYPE,CreateTime) --把执行的sql语句存储在临时表中
       SELECT T.EVENTINFO,CASE @OPTYPE WHEN 1 THEN 'INSERT' WHEN 2 THEN 'UPDATE' WHEN 3 THEN 'DELETE' ELSE ''END,GETDATE() FROM #T T   --从临时表中提取触发的sql语句
    END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

触发器会把数据临时存储在inserted和deleted表中，inserted 和deleted表是逻辑表，只有在触发器中才存在，触发器结束了，就会自动删除，如果是insert操作，则新添加的数据存储在inserted表中，如果是update操作，其实相当于先删除然后再插入，即在deleted表中记录原来的记录，在inserted表中记录更新后的数据，如果是deleted操作，则删除的数据被记录在deleted表中。