-- Trigger: trig_SQLQAUserHistory -- Bill Wunder use admin GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trig_SQLQAUserHistory]') and OBJECTPROPERTY(id, N'IsTrigger') = 1) drop trigger [dbo].[trig_SQLQAUserHistory] GO CREATE TRIGGER trig_SQLQAUserHistory ON admin.dbo.SQLQAUserHistory INSTEAD OF UPDATE, DELETE AS BEGIN set nocount on declare @recipients varchar(100) declare @message varchar(1024) declare @subject varchar(100) declare @buffer varchar(255) declare @login varchar(30) declare @action varchar(10) set @recipients = 'bill.wunder@wallst.com' create table #inputbuffer (EventType varchar(20) , Parameter int , EventInfo varchar(255)) insert #inputbuffer exec sp_executesql N'dbcc inputbuffer(@spid)',N'@spid int',@@spid select @buffer = replace(replace(EventInfo , char(13) , space(1)) , char(10) , space(1)) -- get rid of CR and LF from #inputbuffer select @login = cast(loginame as varchar(30)) , @message = 'login: ' + cast(loginame as varchar(30)) + space(2) + 'host: ' + cast(hostname as varchar(30)) + space(2) + 'input buffer: ' + isnull(@buffer,'DBCC INPUTBUFFER output not available') from master.dbo.sysprocesses where spid = @@spid if (select count(*) from inserted) = 0 -- delete begin if @login = 'sa' delete h from admin.dbo.SQLQAUserHistory h join deleted d on h.LastBatchDt = d.LastBatchDt and h.spid = d.spid else exec sysmon.dbo.safe_sendmail @recipients = @recipients , @subject = 'Attemp to delete rows from SQLQAUserHistory' , @message = @message end else begin if @login = 'sa' update h set LastBatchDt = i.LastBatchDt , spid = i.spid , login = i.login , host = i.host , NTUser = i.NTUser , buffer = i.buffer , LastObservedDt = i.LastObservedDt from admin.dbo.SQLQAUserHistory h join inserted i on h.LastBatchDt = i.LastBatchDt and h.spid = i.spid else exec sysmon.dbo.safe_sendmail @recipients = @recipients , @subject = 'Attemp to update rows from SQLQAUserHistory' , @message = @message end END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO