August 24, 2011 at 5:17 pm
Hi everyone,
I want do auditing using trigger but I want to insert entire row from the base table even though only on column value get change.
Below is the script for what i am trying to achieve. The trigger I wrote is just for Update but is there any way that in one trigger we can wrap the logic for insert,update and delete.And right now in my audit table I am just tracking the color column but in my desired output, I want to insert entire row which got updated instead of just color.
Please guide me to the right path. Thanks
CREATE TABLE [dbo].[Auditboat](
[Newcolor] [varchar](10) NULL,
[Oldcolor] [varchar](10) NULL,
[Users] [varchar](10) NULL,
[Action] [varchar](10) NULL
) ON [PRIMARY]
/****** Object: Table [dbo].[Boat] ******/
CREATE TABLE [dbo].[Boat](
[bid] [int] NULL,
[varchar](10) NULL,
[Name] [varchar](10) NULL
) ON [PRIMARY]
INSERT [dbo].[Auditboat] ([Newcolor], [Oldcolor], [Users], [Action]) VALUES (N'white', NULL, N'dbo', N'insert')
GO
INSERT [dbo].[Auditboat] ([Newcolor], [Oldcolor], [Users], [Action]) VALUES (N'Black', N'white', N'dbo', N'update')
GO
INSERT [dbo].[Auditboat] ([Newcolor], [Oldcolor], [Users], [Action]) VALUES (N'white', N'white', N'dbo', N'update')
GO
INSERT [dbo].[Auditboat] ([Newcolor], [Oldcolor], [Users], [Action]) VALUES (N'white', N'white', N'dbo', N'update')
GO
INSERT [dbo].[Auditboat] ([Newcolor], [Oldcolor], [Users], [Action]) VALUES (N'black', N'white', N'dbo', N'update')
GO
INSERT [dbo].[Auditboat] ([Newcolor], [Oldcolor], [Users], [Action]) VALUES (N'black', N'white', N'dbo', N'update')
GO
INSERT [dbo].[Boat] ([bid], , [Name]) VALUES (1, N'pink', N'suzan')
GO
INSERT [dbo].[Boat] ([bid], , [Name]) VALUES (2, N'blue', N'Michael')
GO
INSERT [dbo].[Boat] ([bid], , [Name]) VALUES (3, N'pink', N'George')
GO
INSERT [dbo].[Boat] ([bid], , [Name]) VALUES (4, N'black', N'David')
GO
INSERT [dbo].[Boat] ([bid], , [Name]) VALUES (5, N'black', N'maria')
GO
--Trigger
CREATE trigger [dbo].[auditting_IUD]
on [dbo].[Boat]
after update
as
begin
insert into Auditboat (Newcolor,Oldcolor,Users,action)
select inserted.color,deleted.color,user,'update'
from inserted inner join deleted
on inserted.bid=deleted.bid
end
GO
August 24, 2011 at 5:18 pm
Sorry wrong title ,,,
Correct one :Insert,update and delete trigger on multiple columns
August 24, 2011 at 5:33 pm
Here's my usual approach to this process. This is going to be mostly psuedocode to give you the general idea.
Assuming you have the following table:
CREATE TABLE tblA (ColA INT, ColB VARCHAR(50), ColC INT)
What you'll do is create the following:
CREATE TABLE Audit_tblA (ColA INT, ColB VARCHAR(50), ColC INT, AuditAction CHAR(3), AuditDate DATETIME Default GETDATE(), AuditUser VARCHAR(150) Default SYSTEM_User())
Then create a few triggers:
CREATE TRIGGER tr_UI_tblA ON tblA FOR Update,Insert
AS
INSERT INTO Audit_tblA
SELECT *, 'I/U', default, default
FROM Inserted
GO
CREATE TRIGGER tr_D_tblA ON tblA FOR Delete
AS
Insert INTO Audit_tblA
SELECT *, 'DEL', default, default
FROM Deleted
GO
What this does is give you a single record per update/insert, and a final record on the deletion, for each row existing in your table. If this audit wasn't created when the table was first built, you'll probably want to run an initialization load to put all existing records into the structure (I personally use 'OLD' as my indicator that it was created during initialization).
You typically won't want to use a single trigger for all 3 unless you want to do double entry auditing, which in my opinion wastes space.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 24, 2011 at 5:43 pm
Example from Stack Overflow:
CREATE TRIGGER DML_ON_TABLEA ON TABLEA AFTER INSERT,DELETE,UPDATE AS BEGIN SET NOCOUNT ON; CASE WHEN (INSERT) THEN -- INSERT ON AUX TABLEB WHEN (DELETE) THEN -- DELETE ON AUX TABLEB ELSE --OR WHEN (UPDATE) THEN -- UPDATE ON AUX TABLEB END END GO
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 24, 2011 at 5:56 pm
Can you please give some more details ?
If its works then its great using case statement,
August 24, 2011 at 6:23 pm
Why don't you experiment with it?
http://msdn.microsoft.com/en-us/library/aa258254(v=sql.80).aspx
The following link demonstrates how to hande a INSERT, UPDATE and DELETE in a single Trigger.
http://technet.microsoft.com/en-us/library/ms189799.aspx
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 24, 2011 at 6:46 pm
Thanks for your response,
It was really helpful but the trigger you created is not worked for the UPDATE. when I am doing the update then it just inserting the new value in the audit table instead of the old value.
August 24, 2011 at 11:07 pm
krishusavalia (8/24/2011)
Thanks for your response,It was really helpful but the trigger you created is not worked for the UPDATE. when I am doing the update then it just inserting the new value in the audit table instead of the old value.
Please post your DDL for the table definition and the Trigger.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 25, 2011 at 6:11 am
CREATE TABLE Audit_tblA (ColA INT
, ColB VARCHAR(50)
, ColC varchar(50)
, AuditAction CHAR(3)
, AuditDate DATETIME Default GETDATE()
, AuditUser VARCHAR(150) Default SYSTEM_User
)
alter TRIGGER tr_UI_tblA ON boat FOR Update,Insert
AS
begin
if update(color)
INSERT INTO Audit_tblA
SELECT inserted.bid,deleted.color,inserted.name, 'U', getdate(),SYSTEM_User
FROM Inserted join deleted on inserted.bid=deleted.bid
ELSE
INSERT INTO Audit_tblA
SELECT *, 'I', getdate(),SYSTEM_User
FROM Inserted
end
GO
August 25, 2011 at 6:18 am
Welsh Corgi (8/24/2011)
Example from Stack Overflow:CREATE TRIGGER DML_ON_TABLEA ON TABLEA AFTER INSERT,DELETE,UPDATE AS BEGIN SET NOCOUNT ON; CASE WHEN (INSERT) THEN -- INSERT ON AUX TABLEB WHEN (DELETE) THEN -- DELETE ON AUX TABLEB ELSE --OR WHEN (UPDATE) THEN -- UPDATE ON AUX TABLEB END END GO
That's not valid T-SQL. Case takes an expression not a value and there's no boolean expression 'INSERT' that says that the trigger was an insert.
If can be done like that, but it takes more logic than that to determine what the operation really was.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2011 at 6:22 am
alter TRIGGER tr_UI_tblA ON boat FOR Update,Insert
AS
begin
if update(color)
INSERT INTO Audit_tblA
SELECT inserted.bid,deleted.color,inserted.name, 'U', getdate(),SYSTEM_User
FROM Inserted join deleted on inserted.bid=deleted.bid
ELSE
INSERT INTO Audit_tblA
SELECT *, 'I', getdate(),SYSTEM_User
FROM Inserted
end
GO
The UPDATE function returns true if the column was specified in the operation. It will always return true for all columns on an insert (because they're all part of the operation), it'll return true on an update only if the column was part of the SET portion of the update.
The usual way to tell if the operation was an insert, update or delete is to check the inserted and deleted tables. Something like this:
alter TRIGGER tr_UI_tblA ON boat FOR Update,Insert
AS
begin
DECLARE @Operation Char(1)
IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
SET @Operation = 'U' -- it's an update
IF EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
SET @Operation = 'I' -- it's an insert
-- rest of trigger logic follows.
END
GO
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2011 at 10:01 am
There's a big drawback on this type of auditing: the audit table is going to be a real hot spot in your database. Every insert, update, delete on every table is going to require an exclusive lock on that table. i.e. everything a user of your system wants to do is going to have to wait for all other user's actions.
To prevent this I'm still generating triggers on the tables that need to be audit-ed, but these triggers do not write to the table themselves. Instead they create an xml message upon each activation which gets posted into an SQL Server Service Broker service. The service then writes the information into the auditing tables. This way the auditing can even be off-loaded onto another server.
One disadvantage however is that not all characters are allowed in xml, i.e. you need to take some special precautions if your texts can contain characters below 'space', other than tab, cr & lf (MS SQL's xml implementation doesn't even allow the escape codes for these values in the xml text). Before I got aware of this problem the product was in production and I was stuck on a format that didn't allow for proper escape methods. But if you're still to start a new implementation you may decide to store the values in some proprietary format, maybe bin64 encoded or so, to avoid this issue.
August 25, 2011 at 2:38 pm
Nice way of doing Insert and update auditing .
But My requirement got changed and now I have to do it using stored proc so It made my work so eazy.
Thanks
August 25, 2011 at 2:39 pm
Thanks Gila,
Nice way of doing Insert and update auditing . I appreciate you solution.
But My requirement got changed and now I have to do it using stored proc so It made my work so eazy.
Thanks
August 25, 2011 at 2:45 pm
GilaMonster (8/25/2011)
That's not valid T-SQL. Case takes an expression not a value and there's no boolean expression 'INSERT' that says that the trigger was an insert.
If can be done like that, but it takes more logic than that to determine what the operation really was.
Right, I've written Triggers in Oracle and all you have to do is specify ON INSERT, UPDATE or DELETE.
I did not intend to give bad information.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply