sql 触发器应用对于工作表的监控

比眉伴天荒 2022-09-30 11:51 141阅读 0赞

create trigger  
       \[dbo\].\[tr\_t\_prc\_status\_addLog\]  
    on \[dbo\].\[t\_prc\_status\]  
   for insert  
     , update  
     , delete  
as

begin

declare @ins\_cnt numeric  
  declare @del\_cnt numeric

select @ins\_cnt = count(\*)  
    from inserted

select @del\_cnt = count(\*)  
    from deleted

if @ins\_cnt = 0  
  begin

insert  
      into dbo.log\_t\_prc\_status  
    select getdate()  
         , 'D'  
         , host\_name()  
         , app\_name()  
         , \*  
      from deleted

end

else  
  begin

if @del\_cnt = 0

insert  
        into dbo.log\_t\_prc\_status  
      select getdate()  
           , 'I'  
           , host\_name()  
           , app\_name()  
           , \*  
        from inserted

else  
    begin

insert  
        into dbo.log\_t\_prc\_status  
      select getdate()  
           , 'u'  
           , host\_name()  
           , app\_name()  
           , \*  
        from deleted

insert  
        into dbo.log\_t\_prc\_status  
      select getdate()  
           , 'U'  
           , host\_name()  
           , app\_name()  
           , \*  
        from inserted

end

\---work table

CREATE TABLE \[dbo\].\[t\_prc\_status\](  
 \[prc\_contents\] \[nvarchar\](3) NOT NULL,  
 \[status\] \[nvarchar\](2) NULL,  
 \[plan\_ym\] \[nvarchar\](6) NULL,  
 \[plan\_times\] \[int\] NULL,  
 \[cal\_remainder\_flg\] \[tinyint\] NOT NULL,  
 \[ins\_emp\_cd\] \[nvarchar\](4) NOT NULL,  
 \[ins\_date\] \[datetime\] NOT NULL,  
 \[upd\_emp\_cd\] \[nvarchar\](4) NOT NULL,  
 \[upd\_date\] \[datetime\] NOT NULL,  
 CONSTRAINT \[pk\_t\_prc\_status\](\[prc\_contents\] ASC)

\---log table

CREATE TABLE \[dbo\].\[log\_t\_prc\_status\](  
 \[log\_date\] \[datetime\] NULL,  
 \[log\_type\] \[char\](1) NULL,  
 \[log\_host\_name\] \[nchar\](100) NULL,  
 \[log\_app\_name\] \[nvarchar\](128) NULL,  
 \[prc\_contents\] \[nvarchar\](3) NULL,  
 \[status\] \[nvarchar\](2) NULL,  
 \[plan\_ym\] \[nvarchar\](6) NULL,  
 \[plan\_times\] \[int\] NULL,  
 \[cal\_remainder\_flg\] \[tinyint\] NULL,  
 \[ins\_emp\_cd\] \[nvarchar\](4) NULL,  
 \[ins\_date\] \[datetime\] NULL,  
 \[upd\_emp\_cd\] \[nvarchar\](4) NULL,  
 \[upd\_date\] \[datetime\] NULL  
)