how to write update trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to write update trigger

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-12-23 : 22:17:08

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 "AUDIT COMMENT".I need write the update trigger to audit column saying record has updated.

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,
Ganga

Thanks,
Gangadhara MS
SQL Developer and DBA

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-12-24 : 01:57:37

[code]
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, 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
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[/code]

KH
[spoiler]Time is always against us[/spoiler]

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-12-24 : 02:09:24

Hi Khathan,

I want the record updated when the record has been been updated i n eed to write with audit comment "System User name--Record updated"
And I need to write with old values to History table.

How can we do this ? Pls help

Thanks,
Gangadhara MS
SQL Developer and DBA

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-12-24 : 03:11:13

for update operation the old value is in DELETED and new value is in INSERTED.

just check for exists of record in both INSERTED & DELETED table and insert into your enquiries_history from DELETED with the corresponding comment.

KH
[spoiler]Time is always against us[/spoiler]

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-24 : 03:54:29

gangadhara: I have a question for you.

When you want to capture only the updated records they why you are writing trigger for capturing Insert & deleted records ?

quote:

create TRIGGER [dbo].[Update_Enquiries] ON [dbo].[enquiries]
FOR INSERT, UPDATE, DELETE AS

Even if you want to capture deleted records then also Insert is not required here. Small change in Khtan's code.

create TRIGGER [dbo].[Update_Enquiries]
ON [dbo].[enquiries]
FOR UPDATE, DELETE
AS
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

Kristen
Test

22859 Posts

Posted - 2010-12-24 : 04:59:02

Why use unstructured CHAR column [auditcomment]? Create two columns for AuditDate and AuditUser - then you can search and report on them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources