March 10, 2016 at 9:15 am
Could you please help me to create a single trigger for both inserts and updates in this case.
Many thanks!
March 10, 2016 at 12:49 pm
SQL!$@w$0ME (3/10/2016)
Could you please help me to create a single trigger for both inserts and updates in this case.Many thanks!
Did you already try the approach with column-level permissions? Have you considered the (IMO much better) alternative to have a separate audit table?
If you really want to, the general outline of the logic for an insert/update trigger in this specific case would be like this:
-- Standard start of any trigger
-- Do not put anything before this. Ever.
IF @@ROWCOUNT = 0 RETURN; -- No rows were affected, bail out.
SET NOCOUNT ON;
IF NOT EXISTS (SELECT * FROM inserted) RETURN; -- Merge statement affected rows but nothing was inserted or updated, bail out.
-- Actual trigger logic below this line
IF EXISTS (SELECT * FROM deleted)
BEGIN;
-- Logic to handle updates goes here
END;
ELSE
BEGIN;
-- Logic to handle inserts goes here
END;
Do not forget that triggers fire once per statement so inserted and deleted can have multiple rows.
March 10, 2016 at 5:51 pm
Thanks a lot Hugo.
Did you already try the approach with column-level permissions? Have you considered the (IMO much better) alternative to have a separate audit table?
Not able to apply in my scenario.
Please validate the code(final).
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TR_Test1_InsertUpdate]
ON [dbo].[Table1]
FOR INSERT, UPDATE
AS
-- Do not put anything before these lines!
IF @@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
IF NOT EXISTS (SELECT * FROM INSERTED) RETURN;
IF EXISTS (SELECT * FROM deleted)
BEGIN
-- Logic to handle updates goes here
UPDATE dbo.[Table1]
SET UpdateDatetime=CURRENT_TIMESTAMP, UpdateUser=SUSER_SNAME()
WHERE EXISTS
(SELECT *
FROM INSERTED AS i
WHERE i.ID = Table1.ID);
END
ELSE
BEGIN
-- Logic to handle Insert goes here
UPDATE dbo.[Table1]
SET CreateDatetime=CURRENT_TIMESTAMP, CreateUser=SUSER_SNAME()
WHERE EXISTS
(SELECT *
FROM INSERTED AS i
WHERE i.ID = Table1.ID);
END
GO
March 11, 2016 at 5:24 am
SQL!$@w$0ME (3/10/2016)
Thanks a lot Hugo.Did you already try the approach with column-level permissions? Have you considered the (IMO much better) alternative to have a separate audit table?
Not able to apply in my scenario.
Why not? Where did you get stuck?
Please validate the code(final).
I see no obvious issues based on a quick visual inspection.
That is not a guarantee that it actually is error-free. Final verification and a full test are all your own responsibility.
Be aware that without column-level permissions, people can still manually change the data in the audit columns.
March 11, 2016 at 5:42 am
Thanks Hugo.
The vendor does not want to track all the history of audit columns. They want to keep the audit information(last insert/update) on the same table. Also want to make sure the users/app should not have the ability to insert/update the audit columns with invalid values or NULL values since these columns have a dependency on reporting users to make their changes based on create/update records time.
Many thanks!
March 11, 2016 at 6:45 am
SQL!$@w$0ME (3/11/2016)
Thanks Hugo.The vendor does not want to track all the history of audit columns. They want to keep the audit information(last insert/update) on the same table. Also want to make sure the users/app should not have the ability to insert/update the audit columns with invalid values or NULL values since these columns have a dependency on reporting users to make their changes based on create/update records time.
Many thanks!
Then you should revisit the column-level permissions idea. Currently, users are still able to change the values in the audit columns.
March 11, 2016 at 7:06 am
Ok thanks.
March 13, 2016 at 5:01 pm
Hi Hugo, I have made some changes to the trigger so that users won't be able to make updates on the audit columns.
Can you please take a look into this and advise.
Also can you please help to modify the script in way that users are still able to update the row, but keep the audit columns unchanged even if they have changed these values.
/****** Object: Trigger [dbo].[TR_Test1_InsertUpdate] Script Date: 3/13/2016 6:16:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TR_Test1_InsertUpdate]
ON [dbo].[Table1]
FOR INSERT, UPDATE
AS
-- Do not put anything before these lines!
IF @@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
IF NOT EXISTS (SELECT * FROM INSERTED) RETURN;
IF EXISTS (SELECT * FROM DELETED)
BEGIN
-- Logic to handle updates goes here
IF UPDATE(CreateUser) OR UPDATE(CreateDatetime) OR UPDATE(UpdateUser) OR UPDATE(UpdateDatetime)
--Deny update of Audit Create Columns
BEGIN
--select * from inserted;
--select * from deleted;
ROLLBACK TRANSACTION
RAISERROR('Changes to Audit columns(CreateUser, CreateDate, UpdateUser, UpdateDate) are not allowed', 16, 1);
print 'Denied update of Audit Columns'
RETURN;
END
print 'updating table'
UPDATE dbo.[Table1]
SET UpdateDatetime=GETDATE(), UpdateUser=SUSER_SNAME()
WHERE EXISTS
(SELECT *
FROM INSERTED AS i
WHERE i.ID = Table1.ID);
print 'Update completed'
END
ELSE
BEGIN
-- Logic to handle Insert goes here
Print 'Inserting to table'
UPDATE dbo.[Table1]
SET CreateDatetime=GETDATE(), CreateUser=SUSER_SNAME()
WHERE EXISTS
(SELECT *
FROM INSERTED AS i
WHERE i.ID = Table1.ID);
print 'Insert completed'
END
March 15, 2016 at 2:12 pm
SQL!$@w$0ME (3/13/2016)
Hi Hugo, I have made some changes to the trigger so that users won't be able to make updates on the audit columns.Can you please take a look into this and advise.
As I already said, you should not rely on my advise on this. Test it. Test the "happy flow" first. Then switch gears and throw every nasty scenario you can imagine at it and try to break the code.
Also can you please help to modify the script in way that users are still able to update the row, but keep the audit columns unchanged even if they have changed these values.
As mentioned in my previous reply, the best way to do this is to use column-levevl permissions. Which as an added bonus allows you to remove the insert trigger, which in turn means that the recursie triggering no longer applies so you can simplify the logic in the update trigger.
March 16, 2016 at 6:39 am
Thanks Hugo. Is there a simple way to deny permissions on few columns on all tables on a database for all users except sysadmins.
March 16, 2016 at 2:49 pm
SQL!$@w$0ME (3/16/2016)
Thanks Hugo. Is there a simple way to deny permissions on few columns on all tables on a database for all users except sysadmins.
I would run a query on sys.tables and sys.columns to generate the DCL statements for all existing tables, and then set up a good, controlled procedure to ensure I always set (or re-set) the permissions when tables are created or recreated.
March 16, 2016 at 8:34 pm
Eirikur Eiriksson (3/9/2016)
SQL!$@w$0ME (3/9/2016)
Users are still able to insert an invalid date/user in the Audit columns with default constraints. What is the best way to avoid this.Thanks!
Trigger
😎
Second that... trigger. And you might want to make it an Instead Of trigger so that once the values are initially set, no one can change them unless they disable the trigger... and you need to have a serious talk in the wood shed with whomever does such a thing. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2016 at 8:44 pm
SQL!$@w$0ME (3/10/2016)
Thanks a lot Hugo.Did you already try the approach with column-level permissions? Have you considered the (IMO much better) alternative to have a separate audit table?
Not able to apply in my scenario.
Please validate the code(final).
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TR_Test1_InsertUpdate]
ON [dbo].[Table1]
FOR INSERT, UPDATE
AS
-- Do not put anything before these lines!
IF @@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
IF NOT EXISTS (SELECT * FROM INSERTED) RETURN;
IF EXISTS (SELECT * FROM deleted)
BEGIN
-- Logic to handle updates goes here
UPDATE dbo.[Table1]
SET UpdateDatetime=CURRENT_TIMESTAMP, UpdateUser=SUSER_SNAME()
WHERE EXISTS
(SELECT *
FROM INSERTED AS i
WHERE i.ID = Table1.ID);
END
ELSE
BEGIN
-- Logic to handle Insert goes here
UPDATE dbo.[Table1]
SET CreateDatetime=CURRENT_TIMESTAMP, CreateUser=SUSER_SNAME()
WHERE EXISTS
(SELECT *
FROM INSERTED AS i
WHERE i.ID = Table1.ID);
END
GO
Instead of SUSER_SNAME, consider using ORIGINAL_LOGIN() in case impersonation ever occurs through the use of such things as EXECUTE AS OWNER, etc.
Also, I'm confused. In the UPDATE part of your trigger, you've done nothing to prevent the mutilation of the CREATExxx columns. They should not be allowed to be update even when someone tries to change them manually.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 17, 2016 at 8:27 am
Found this sample in my collection, probably posted it somewhere before, feel free to play around with it.
😎
USE TEEST;
GO
SET NOCOUNT ON;
/* DDL Audit table
--TRUNCATE TABLE dbo.TBL_TEST_DDL_AUDIT
*/
IF OBJECT_ID(N'dbo.TBL_TEST_DDL_AUDIT') IS NULL
BEGIN
CREATE TABLE dbo.TBL_TEST_DDL_AUDIT
(
DDLA_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_DDL_AUDIT_DDLA_ID PRIMARY KEY CLUSTERED
,DDLA_XML XML NOT NULL
);
END
GO
/*
AUDID DDL:
CREATE_TABLE
,ALTER_TABLE
,DROP_TABLE
,CREATE_TRIGGER
,ALTER_TRIGGER
,DROP_TRIGGER
--DROP TRIGGER DDL_AUDIT_DATABASE_TEEST ON DATABASE
*/
IF
(
SELECT OBJECT_ID FROM sys.triggers
WHERE name = N'DDL_AUDIT_DATABASE_TEEST'
) IS NULL
BEGIN
EXEC (N'
CREATE TRIGGER DDL_AUDIT_DATABASE_TEEST
ON DATABASE
FOR CREATE_TABLE
,ALTER_TABLE
,DROP_TABLE
,CREATE_TRIGGER
,ALTER_TRIGGER
,DROP_TRIGGER
AS
BEGIN
INSERT INTO dbo.TBL_TEST_DDL_AUDIT(DDLA_XML)
SELECT EVENTDATA()
END');
END
GO
/* Drop the foreign key constraint before re-creating the objects */
IF OBJECT_ID(N'FK_DBO_TBL_TEST_DELETE_TRIGGER_AUDIT_DBO_TBL_TEST_DELETE_TRIGGER_TDT_ID') IS NOT NULL
BEGIN
ALTER TABLE dbo.TBL_TEST_DELETE_TRIGGER_AUDIT DROP CONSTRAINT FK_DBO_TBL_TEST_DELETE_TRIGGER_AUDIT_DBO_TBL_TEST_DELETE_TRIGGER_TDT_ID;
END
/* The table we want to audit and prevent any modification */
IF OBJECT_ID(N'dbo.TBL_TEST_DELETE_TRIGGER') IS NOT NULL DROP TABLE dbo.TBL_TEST_DELETE_TRIGGER;
CREATE TABLE dbo.TBL_TEST_DELETE_TRIGGER
(
TDT_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_DELETE_TRIGGER_TDT_ID PRIMARY KEY CLUSTERED
,TDT_XVAL INT NOT NULL
,TDT_USER SYSNAME NOT NULL DEFAULT (ORIGINAL_LOGIN())
,TDT_TS DATETIME NOT NULL DEFAULT (GETDATE())
);
/* The audit table */
IF OBJECT_ID(N'dbo.TBL_TEST_DELETE_TRIGGER_AUDIT') IS NOT NULL DROP TABLE dbo.TBL_TEST_DELETE_TRIGGER_AUDIT;
CREATE TABLE dbo.TBL_TEST_DELETE_TRIGGER_AUDIT
(
TTDTA_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_DELETE_TRIGGER_AUDIT_TTDTA_ID PRIMARY KEY CLUSTERED
,TDT_ID INT NOT NULL CONSTRAINT FK_DBO_TBL_TEST_DELETE_TRIGGER_AUDIT_DBO_TBL_TEST_DELETE_TRIGGER_TDT_ID FOREIGN KEY REFERENCES dbo.TBL_TEST_DELETE_TRIGGER(TDT_ID)
,TDT_CNT INT NOT NULL
,TTDTA_USER SYSNAME NOT NULL CONSTRAINT DFLT_DBO_TBL_TEST_DELETE_TRIGGER_AUDIT_TTDTA_USER DEFAULT (ORIGINAL_LOGIN())
,TTDTA_CUSER SYSNAME NOT NULL CONSTRAINT DFLT_DBO_TBL_TEST_DELETE_TRIGGER_AUDIT_TTDTA_CUSER DEFAULT (CURRENT_USER)
,TDT_XML XML NOT NULL
)
GO
/* Trigger to write any modification attempts to the audit table */
CREATE TRIGGER dbo.TRG_TEST_DELETE_TRIGGER_NO_DELETE
ON dbo.TBL_TEST_DELETE_TRIGGER
INSTEAD OF UPDATE, DELETE
AS
BEGIN
DECLARE @X INT = 0;
INSERT INTO dbo.TBL_TEST_DELETE_TRIGGER_AUDIT(TDT_ID, TDT_CNT, TDT_XML)
/* UPDATE ATTEMPT */
SELECT TOP (1)
I.TDT_ID
,COUNT(*) OVER ( PARTITION BY (SELECT NULL))
,(SELECT TOP (1)
*
FROM inserted
ORDER BY TDT_ID ASC
FOR XML PATH('UPDATE'),ROOT('AUDIT'),TYPE
) AS TXML
FROM inserted I
LEFT OUTER JOIN deleted D
ON I.TDT_ID = D.TDT_ID
WHERE D.TDT_ID IS NOT NULL
UNION ALL
/* DELETION ATTEMPT */
SELECT TOP (1)
D.TDT_ID
,COUNT(*) OVER ( PARTITION BY (SELECT NULL))
,(SELECT TOP (1)
*
FROM deleted
ORDER BY TDT_ID ASC
FOR XML PATH('DELETE'),ROOT('AUDIT'),TYPE
) AS TXML
FROM deleted D
LEFT OUTER JOIN inserted I
ON D.TDT_ID = I.TDT_ID
WHERE I.TDT_ID IS NULL;
RAISERROR ('NO UPDATES OR DELETES ALLOWED!',0,0) WITH NOWAIT;
END
GO
/* Prevent modifications on the audit */
CREATE TRIGGER dbo.TBL_TEST_DELETE_TRIGGER_AUDIT_NO_CHANGE
ON dbo.TBL_TEST_DELETE_TRIGGER_AUDIT
INSTEAD OF UPDATE, DELETE
AS
BEGIN
/* nothing will happen here */
SELECT NULL
END
GO
/* Insert few values into the audited table */
INSERT INTO dbo.TBL_TEST_DELETE_TRIGGER(TDT_XVAL)
VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
/* Verify the table's content */
SELECT
TDT.TDT_ID
,TDT.TDT_XVAL
,TDT_USER
,TDT_TS
FROM dbo.TBL_TEST_DELETE_TRIGGER TDT;
/* Deletion attempt which fails */
DELETE TDT
FROM dbo.TBL_TEST_DELETE_TRIGGER TDT
WHERE TDT.TDT_ID > 5;
SELECT
TDT.TDT_ID
,TDT.TDT_XVAL
,TDT_USER
,TDT_TS
FROM dbo.TBL_TEST_DELETE_TRIGGER TDT;
/* Update attempt which fails */
UPDATE X
SET X.TDT_XVAL = 0
FROM dbo.TBL_TEST_DELETE_TRIGGER X
WHERE X.TDT_ID > 8;
SELECT
TDTA.TTDTA_ID
,TDT_ID
,TDT_CNT
,TTDTA_USER
,TTDTA_CUSER
,TDTA.TDT_XML
FROM TBL_TEST_DELETE_TRIGGER_AUDIT TDTA;
/* The foreign key constraints prevents drop table */
BEGIN TRY
TRUNCATE TABLE dbo.TBL_TEST_DELETE_TRIGGER;
END TRY
BEGIN CATCH
RAISERROR('Naughty naughty!! No Drop allowed!',0,0);
END CATCH
/* The foreign key constraints prevents truncation too */
BEGIN TRY
DROP TABLE dbo.TBL_TEST_DELETE_TRIGGER;
END TRY
BEGIN CATCH
RAISERROR('Naughty naughty!! No truncation allowed!',0,0);
END CATCH
GO
/*
View all audited DDL events
*/
SELECT
TDA.DDLA_ID
,TDA.DDLA_XML.value('(/EVENT_INSTANCE/EventType/text())[1]' ,'NVARCHAR(128)') AS EventType
,TDA.DDLA_XML.value('(/EVENT_INSTANCE/PostTime/text())[1]' ,'DATETIME' ) AS PostTime
,TDA.DDLA_XML.value('(/EVENT_INSTANCE/SPID/text())[1]' ,'INT' ) AS SPID
,TDA.DDLA_XML.value('(/EVENT_INSTANCE/ServerName/text())[1]' ,'NVARCHAR(128)') AS ServerName
,TDA.DDLA_XML.value('(/EVENT_INSTANCE/LoginName/text())[1]' ,'NVARCHAR(128)') AS LoginName
,TDA.DDLA_XML.value('(/EVENT_INSTANCE/UserName/text())[1]' ,'NVARCHAR(128)') AS UserName
,TDA.DDLA_XML.value('(/EVENT_INSTANCE/DatabaseName/text())[1]' ,'NVARCHAR(128)') AS DatabaseName
,TDA.DDLA_XML.value('(/EVENT_INSTANCE/SchemaName/text())[1]' ,'NVARCHAR(128)') AS SchemaName
,TDA.DDLA_XML.value('(/EVENT_INSTANCE/ObjectName/text())[1]' ,'NVARCHAR(128)') AS ObjectName
,TDA.DDLA_XML.value('(/EVENT_INSTANCE/ObjectType/text())[1]' ,'NVARCHAR(128)') AS ObjectType
,TDA.DDLA_XML.value('(/EVENT_INSTANCE/TSQLCommand/CommandText/text())[1]','nvarchar(max)') AS CommandText
FROM dbo.TBL_TEST_DDL_AUDIT TDA;
Viewing 14 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply