December 23, 2010 at 8:03 pm
Dear All,
I have created the below trigger on a main table and we have exact schema of main table as History table with one more column for as "AUDIT COMMENT".
create TRIGGER [dbo].[Update_Enquiries] ON [dbo].[enquiries]
FOR INSERT, UPDATE, DELETE
AS
IF ( SELECT count(*) from INSERTED ) > 0 BEGIN
INSERT INTO enquiries_history (ID, date_modified, modified_by, HCP_ID, consumer_ID, product_ID, logger_ID, enquiry_status_ID, summary, entry_channel_ID, enquiry_category_ID, comment)
SELECT ID, date_modified, modified_by, HCP_ID, consumer_ID, product_ID, logger_ID, enquiry_status_ID, summary, entry_channel_ID, enquiry_category_ID, comment
FROM INSERTED
update enquiries_history
set auditcomment = Convert(varchar, getdate(), 9) + ' - '+ SYSTEM_USER + ' - Record inserted.'
where (auditcomment='' or auditcomment is NULL)
and id in
(select t.id
from enquiries_history t
left join inserted d on t.id=d.id
and t.date_modified = d.date_modified)
END
ELSE BEGIN
INSERT INTO enquiries_history (ID, date_modified, modified_by, HCP_ID, consumer_ID, product_ID, logger_ID, enquiry_status_ID, summary, entry_channel_ID, enquiry_category_ID, comment)
SELECT ID, date_modified, modified_by, HCP_ID, consumer_ID, product_ID, logger_ID, enquiry_status_ID, summary, entry_channel_ID, enquiry_category_ID, comment
FROM DELETED
update enquiries_history
set auditcomment = Convert(varchar, getdate(), 9) + ' - '+ SYSTEM_USER + ' - Record deleted.'
where (auditcomment='' or auditcomment is NULL)
and id in
(select t.id
from enquiries_history t
left join deleted d on t.id=d.id
and t.date_modified = d.date_modified)
END
The issue is when i update the record in the main table it should record the old values in history table as updated with audit comment "Record updated".
How do we accomplish this as well in the above trigger.
Please help.
Thanks,
Gang
December 24, 2010 at 2:47 am
Updated records have a row in the INSERTED and the DELETED table.
Try something like this...
IF ( SELECT count(*) from INSERTED ) > 0 AND ( SELECT count(*) from DELETED ) > 0
BEGIN
--We have an update!!!
--Because we do not want to add another 2 records for inserted and updated...
RETURN
END
December 26, 2010 at 10:40 pm
Hi,
My concern here is to have when ever insert has done i need to insert that record to History table" also when updated i need to track old values and new values in the History table.
So for that reason i have done like this.pls let me know if this is fine (want to know any other impact.
ALTER TRIGGER [dbo].[Update_Enquiries]
ON [dbo].[enquiries]
FOR INSERT, UPDATE, DELETE
AS
IF ( SELECT count(*) from INSERTED ) > 0
BEGIN
INSERT INTO enquiries_history (ID, date_modified, modified_by, HCP_ID, consumer_ID, product_ID, logger_ID, enquiry_status_ID, summary, entry_channel_ID, enquiry_category_ID, comment, auditcomment)
SELECT ID, date_modified, modified_by, HCP_ID, consumer_ID, product_ID, logger_ID, enquiry_status_ID, summary, entry_channel_ID, enquiry_category_ID, comment, Convert(varchar, getdate(), 9) + ' - '+ SYSTEM_USER + ' - Record inserted.'
FROM INSERTED
--Added for Update statement
IF ( SELECT count(*) from INSERTED ) > 0 and (SELECT count(*) from deleted ) >0
BEGIN
INSERT INTO enquiries_history (ID, date_modified, modified_by, HCP_ID, consumer_ID, product_ID, logger_ID, enquiry_status_ID, summary, entry_channel_ID, enquiry_category_ID, comment)
SELECT ID, date_modified, modified_by, HCP_ID, consumer_ID, product_ID, logger_ID, enquiry_status_ID, summary, entry_channel_ID, enquiry_category_ID, comment
FROM deleted
update enquiries_history
set auditcomment = Convert(varchar, getdate(), 9) + ' - '+ SYSTEM_USER + ' - Record updated.'
where (auditcomment='' or auditcomment is NULL)
and id in
(select t.id
from enquiries_history t
left join inserted d on t.id=d.id
and t.date_modified = d.date_modified)
END --Till here for update trigger
END
ELSE
BEGIN
INSERT INTO enquiries_history (ID, date_modified, modified_by, HCP_ID, consumer_ID, product_ID, logger_ID, enquiry_status_ID, summary, entry_channel_ID, enquiry_category_ID, comment, auditcomment)
SELECT ID, date_modified, modified_by, HCP_ID, consumer_ID, product_ID, logger_ID, enquiry_status_ID, summary, entry_channel_ID, enquiry_category_ID, comment, Convert(varchar, getdate(), 9) + ' - '+ SYSTEM_USER + ' - Record deleted.'
FROM DELETED
END
December 28, 2010 at 10:00 am
1) counting records can be VERY inefficient! all you care about in your logic really is if there is ANY row. think of if a batch updated 1M rows - you would waste the effort counting ALL of them just to see if there were any. 🙂 use IF EXISTS instead.
2) before I give you a refactored version of your trigger, you have failed to address what you want done if a record is DELETED. I imagine you want to make a comment on the history record(s) that state the record was deleted, but need to know for sure first. I also note that your code would update ALL history records for every update. Is that intentional?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 28, 2010 at 11:49 am
Please take a look at these articles on this subject: http://qa.sqlservercentral.com/articles/Auditing/63247/ and http://qa.sqlservercentral.com/articles/Auditing/63248/
There are examples there of efficient ways to log data changes. The discussions on them have a lot of useful information, so be sure to read those as well.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 29, 2010 at 6:47 am
I would do something like this:
declare @comment1 varchar(100), @comment2 varchar(100)
if exists(select 1 from inserted)
if exists(select 1 from deleted) select @comment1 = 'updated - new', @comment2 = 'updated - old'
else set @comment1 = 'inserted'
else set @comment2 = 'deleted'
insert into history(...) select ..., @comment1 from inserted -- 0 rows for delete
insert into history(...) select ..., @comment2 from deleted -- 0 rows for insert
This will work for all situations (ins/upd/del), and have old&new values recorded during update.
December 29, 2010 at 7:49 am
Vedran Kesegic (12/29/2010)
I would do something like this:
declare @comment1 varchar(100), @comment2 varchar(100)
if exists(select 1 from inserted)
if exists(select 1 from deleted) select @comment1 = 'updated - new', @comment2 = 'updated - old'
else set @comment1 = 'inserted'
else set @comment2 = 'deleted'
insert into history(...) select ..., @comment1 from inserted -- 0 rows for delete
insert into history(...) select ..., @comment2 from deleted -- 0 rows for insert
This will work for all situations (ins/upd/del), and have old&new values recorded during update.
I think you need one more part to that to update existing history records when an update is fired. But this can be handled with a simple flag to tell you which kind of action was done (which you should have anyway - there is no need to fire BOTH of your inserts since they can never happen simultaneously. Another recommendation for your code is to ALWAYS use BEGIN/END blocks for IF/ELSE code - I cannot count how many times I have fixed broken logic introduced because of that shortcoming. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 29, 2010 at 8:14 am
Operation = triggering operation (insert,update,delete)
N = number of rows you inserted,updated or deleted in triggering table
#Rows INSERTED = number of rows in INSERTED pseudo-table the trigger code will see
#Rows DELETED = number of rows in DELETED pseudo-table the trigger code will see
#Rows in #Rows in
Operation INSERTED DELETED
--------------------------
INSERT N 0
DELETE 0 N
UPDATE N N
E.g. you update 200 rows with single UPDATE statement, there will be 200 rows in INSERTED and 200 rows in UPDATED pseudo-table.
In this trigger, when update is fired, history table will get 2 rows for each row updated in triggering table: one with old and one with new values.
There is no need to update history, in fact, it is a very bad thing to do.
Update is performance-expensive operation and if something can be achieved in a single insert, it is much faster than doing insert and update very same row just inserted in step before.
I agree with begin-end, this was a typing lazyness 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply