|
Execute the following
SQL Server T-SQL scripts in Management Studio Query Editor to create trigger protection for the Audit Trail table against unauthorized updates and deletes.
-- SQL Server instead of update and delete trigger
-- SQL instead of trigger - SQL audit log table
USE tempdb;
CREATE TABLE Audit (
AuditID INT IDENTITY PRIMARY KEY,
EventTime DATETIME DEFAULT (getdate()),
DESCRIPTION VARCHAR(512))
GO
INSERT INTO Audit
(DESCRIPTION)
VALUES ('General Ledger process completed OK')
INSERT INTO Audit
(DESCRIPTION)
VALUES ('Accounts Payable night job failed')
INSERT INTO Audit
(DESCRIPTION)
VALUES ('Inventory daily processing succeeded')
INSERT INTO Audit
(DESCRIPTION)
VALUES ('Accounts Receivables weekly report succeeded')
GO
CREATE TRIGGER trigAuditGuard
ON Audit
INSTEAD OF UPDATE,DELETE
AS
BEGIN
INSERT INTO Audit
(DESCRIPTION)
SELECT 'Direct audit update, delete denied to >' + USER + '< for AuditID ' + convert(VARCHAR,AuditID) + ' New: ' + DESCRIPTION
FROM Inserted
INSERT INTO Audit
(DESCRIPTION)
SELECT 'Direct audit update, delete denied to >' + USER + '< for AuditID ' + convert(VARCHAR,AuditID) + ' Old: ' + DESCRIPTION
FROM Deleted
END
GO
UPDATE Audit
SET DESCRIPTION = 'Accounts Payable night job succeeded'
WHERE AuditID = 2
GO
DELETE FROM Audit
WHERE AuditID = 4
GO
SELECT *
FROM Audit
GO
/* Results
AuditID EventTime Description
1 2019-02-21 14:57:15.233 General Ledger process completed OK
2 2019-02-21 14:57:15.233 Accounts Payable night job failed
3 2019-02-21 14:57:15.233 Inventory daily processing succeeded
4 2019-02-21 14:57:15.233 Accounts Receivables weekly report succeeded
5 2019-02-21 14:57:15.280 Direct audit update, delete denied to >dbo< for AuditID 2 New: Accounts Payable night job succeeded
6 2019-02-21 14:57:15.280 Direct audit update, delete denied to >dbo< for AuditID 2 Old: Accounts Payable night job failed
7 2019-02-21 14:57:15.280 Direct audit update, delete denied to >dbo< for AuditID 4 Old: Accounts Receivables weekly report succeeded
*/
-- Cleanup
DROP TABLE tempdb.dbo.Audit
GO
Related article:
Kalen Delaney: INSTEAD OF Triggers
Keep data changes in check with this new trigger type
|
