October 5, 2012 at 3:33 am
Hi All,
I am asked to write a trigger for monitoring purpose..
There is one table named Employeee
Now If any record is deleted from this table then i should be notified that deletion is happened on the table..
Can anyone help me with exact query????
************************************
Every Dog has a Tail !!!!! :-D
October 5, 2012 at 6:44 am
here's a full featured example of a trigger that sends an email, taking special care to get all rows affected, and a bunch of audit info;
CREATE TABLE WHATEVER(
WHATEVERID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
DESCRIP VARCHAR(30)
)
GO
CREATE TRIGGER TR_WHATEVER_NOTIFICATIONS
ON WHATEVER FOR DELETE
AS
BEGIN
SET NOCOUNT ON
--gather the information, making sure you get it from the DELETED virtual table, and not the full table
DECLARE @CAPTUREDSTRING VARCHAR(max)
--In this example i want a comma delimited list of important facts about what was inserted.
--using the xml technique to make my comma delimited string.
SELECT @CAPTUREDSTRING = [DeletedItems]
FROM (
SELECT TOP 1 GETDATE()As TheDate,stuff(( SELECT ',' + DESCRIP
FROM INSERTED s2
--WHERE s2.WHATEVERID= s1.WHATEVERID --- must match GROUP BY below
WHERE 1 = 1
ORDER BY DESCRIP
FOR XML PATH('')
),1,1,'') as [DeletedItems]
FROM DELETED s1
GROUP BY s1.WHATEVERID --- without GROUP BY multiple rows are returned
ORDER BY s1.WHATEVERID) myAlias
--now email the results.
declare @htmlbody varchar(max)
SELECT @htmlbody =
'Deleted Item Notification on the Whatever Table '
+ CONVERT( VARCHAR( 20 ), GETDATE(), 113 )
+ '
<P> Auudit Information On The Event:<P>'
+ 'EventDate :' + CONVERT(VARCHAR(30),getdate() ,112) + '
' + CHAR(13) + CHAR(10)
+ 'DBName :' + DB_NAME() + '
' + CHAR(13) + CHAR(10)
+ 'CurrentUser :' + CURRENT_USER + '
' + CHAR(13) + CHAR(10)
+ 'HostName :' + HOST_NAME() + '
' + CHAR(13) + CHAR(10)
+ 'ApplicationName :' + APP_NAME() + '
' + CHAR(13) + CHAR(10)
+ 'ProcedureName :' + ISNULL(OBJECT_NAME(@@PROCID) ,'') + '
' + CHAR(13) + CHAR(10)
+ 'Userid :' + CONVERT(VARCHAR(30),USER_ID() ) + '
' + CHAR(13) + CHAR(10)
+ 'UserName :' + USER_NAME() + '
' + CHAR(13) + CHAR(10)
+ 'sUserid :' + CONVERT(VARCHAR(30),SUSER_ID() ) + '
' + CHAR(13) + CHAR(10)
+ 'sUserName :' + SUSER_SNAME() + '
' + CHAR(13) + CHAR(10)
+ '[ORIGINAL_LOGIN] :' + ORIGINAL_LOGIN() + '
' + CHAR(13) + CHAR(10)
+ 'net_transport :' + CONVERT(VARCHAR(30),ISNULL(ConnectionProperty('net_transport'),'') ) + '
' + CHAR(13) + CHAR(10)
+ 'protocol_type :' + CONVERT(VARCHAR(30),ISNULL(ConnectionProperty('protocol_type'),'') ) + '
' + CHAR(13) + CHAR(10)
+ 'auth_scheme :' + CONVERT(VARCHAR(30),ISNULL(ConnectionProperty('auth_scheme'),'') ) + '
' + CHAR(13) + CHAR(10)
+ 'local_net_address :' + CONVERT(VARCHAR(30),ISNULL(ConnectionProperty('local_net_address'),'') ) + '
' + CHAR(13) + CHAR(10)
+ 'local_tcp_port :' + CONVERT(VARCHAR(30),ISNULL(ConnectionProperty('local_tcp_port'),'') ) + '
' + CHAR(13) + CHAR(10)
+ 'client_net_address :' + CONVERT(VARCHAR(30),ISNULL(ConnectionProperty('client_net_address'),'') ) + '
' + CHAR(13) + CHAR(10)
+ 'physical_net_transport :' + CONVERT(VARCHAR(30),ISNULL(ConnectionProperty('physical_net_transport'),'') ) + '
' + CHAR(13) + CHAR(10)
+ '
<P> The following new items were deleted from the table:<P>'
+ @CAPTUREDSTRING --this has the list of deleted values
+ '
'
EXEC msdb.dbo.sp_send_dbmail
@profile_name='Default Mail Account',
@recipients='lowell@somedomain.com',
@subject = 'Deleted Item Notification',
@body = @htmlbody,
@body_format = 'HTML'
END --TRIGGER
GO
INSERT INTO WHATEVER(DESCRIP)
SELECT 'APPLES' UNION
SELECT 'ORANGES' UNION
SELECT 'BANANAS' UNION
SELECT 'GRAPES' UNION
SELECT 'CHERRIES' UNION
SELECT 'KIWI'
--now delete something:
DELETE FROM WHATEVER WHERE DESCRIP IN('CHERRIES','KIWI','STEAK SANDWICH')
Lowell
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply