April 28, 2023 at 5:17 am
Hi,
I have a trigger which main purpose is to trigger alert whenever there is changes at DB Object and schema e.g Create/AlTER/Drop procedure,table, Views etc.
The trigger works fine and trigger alert as expected.
However I have one scenario where I want trigger to exclude one or two database tables even there is schema change on table.
E.g. I don't want to trigger send email for schema change on Table A and Table B.
How I can achieve this? Below is my trigger script.
CREATE TRIGGER [DB_Trig]
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
ALTER_SCHEMA, RENAME, CREATE_VIEW, ALTER_VIEW,
CREATE_FUNCTION, ALTER_FUNCTION, ALTER_TABLE, DROP_TABLE, ALTER_TRIGGER, DROP_TRIGGER
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EventData xml = EVENTDATA(),
@ip varchar(48) = CONVERT(varchar(48), CONNECTIONPROPERTY('client_net_address'));
DECLARE
@subject nvarchar(max) = N'',
@body varchar(300) = N'',
@recipient varchar(400) = 'xyz@....com',
@db sysname = DB_NAME(),
@schema sysname = @EventData.value(N'(/EVENT_INSTANCE/SchemaName)[1]', N'nvarchar(255)'),
@object sysname = @EventData.value(N'(/EVENT_INSTANCE/ObjectName)[1]', N'nvarchar(255)'),
@event sysname = @EventData.value(N'(/EVENT_INSTANCE/EventType)[1]', N'nvarchar(100)');
BEGIN TRY
SET @subject = @@SERVERNAME + N' : ' + @event + N' : ' + @object;
SET @body = CONVERT(varchar(max), @EventData);
EXEC msdb.dbo.sp_send_dbmail
@profile_name = N'DBServer',
@recipients = @recipient,
@subject = @subject,
@body = @body;
END TRY
BEGIN CATCH
PRINT 'error';
END CATCH
END
April 28, 2023 at 5:24 am
I would think you'd need to just do this:
...
BEGIN TRY
IF @object NOT IN ('TableA', 'TableB') --<<--
BEGIN --<<--
SET @subject = @@SERVERNAME + N' : ' + @event + N' : ' + @object;
SET @body = CONVERT(varchar(max), @EventData);
EXEC msdb.dbo.sp_send_dbmail
@profile_name = N'DBServer',
@recipients = @recipient,
@subject = @subject,
@body = @body;
END --<<--
END TRY
BEGIN CATCH
PRINT 'error';
END CATCH
END
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 3, 2023 at 6:19 am
This was removed by the editor as SPAM
May 22, 2023 at 7:38 am
Thank you. This seems good idea.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply