Another audit table trigger Q - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Another audit table trigger Q

Author

Topic

KimJ
Starting Member

38 Posts

Posted - 2005-02-11 : 11:12:01

Hi -

I've been searching for a solution for this for a while, but either I just don't understand what people are doing, or solutions just don't fit my needs.

Trigger on updates & deletes log to an identical audit table. I have the classic problem of having to update a bunch of rows, which the trigger won't do. I get the 'subquery returned more than 1 value...' error.

Here is the trigger.

CREATE TRIGGER [Add_ClientHistory] ON dbo.ContractMain
FOR UPDATE, DELETE
AS

Update ContractMain set ModifiedDate = getdate() where ContractNumber = (Select ContractNumber from Inserted)

Insert Into ContractAuditLog
(
[ContractNumber] ,
[ContractName] ,
[AffiliatedCenters] ,
[AccountantID] ,
[ContractStatus],
[Notes],
[EnteredBy] ,
[EnteredDate],
[ModifiedBy] ,
[RecordStatus]
)
(Select
[ContractNumber] ,
[ContractName] ,
[AffiliatedCenters] ,
[AccountantID] ,
[ContractStatus],
[Notes],
[EnteredBy] ,
[EnteredDate],
[ModifiedBy] ,
[RecordStatus]
from Deleted)

Notice that I also have a statement to update a date field on the main table. I did it this way because if I had a seperate trigger to update the date, the trigger fires more than once.

Any suggestions?

Thanks in advance,
Kim

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-11 : 11:25:37

you need to join to inserted instead of you subquery (for multiple record updates)


Update cm set 
	cm.ModifiedDate = getdate() 
From	ContractMain cm
JOIN	Inserted i ON cm.ContractNumber = i.ContractNumber

Be One with the Optimizer
TG

KimJ
Starting Member

38 Posts

Posted - 2005-02-11 : 11:36:56

That worked perfectly - Thank you so much TG!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources