Triggers fail on INSERT/DELETE/UPDATE in DTS

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

Import/Export (DTS) and Replication (2000)

Triggers fail on INSERT/DELETE/UPDATE in DTS

Author

Topic

littlewing
Starting Member

33 Posts

Posted - 2005-08-19 : 16:01:40

Hello, I have a DTS package that inserts/updates/deletes records on a table - standard stuff. I have triggers attached to the table so that on insert/update/delete I write to an audit log table of the event and the entity affected.

here's my insert trigger:

CREATE Trigger AuditAgentInsert
ON [dbo].[ladAgents]
AFTER INSERT
AS

DECLARE @MemberID int;
SELECT @MemberID = MemberID
FROM inserted;

INSERT INTO ladAuditLog(EntityID, EntityType, ActionType, CreateDate)
VALUES (@MemberID, 'Agent', 'INSERT', GetDate())

If I run insert/update/delete statements from Query Analyzer the triggers fire, all is well. But when the DTS package runs and inserts/updates/deletes occur on the table the DTS package fails
saying I cannot insert NULL into EntityID field in my audit log table. Fair enough but I'm wondering why that @MemberID is not getting picked up here?

Anyone have any ideas? Permissions? Something I'm missing in DTS?

Thank you.
LW

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-19 : 16:23:05

Triggers operate on a result set not row by row, so you can't use variables like you have in your trigger. Instead, you'll want to do this:

INSERT INTO ladAuditLog(EntityID, EntityType, ActionType, CreateDate)
SELECT MemberID, 'Agent', 'INSERT', GETDATE()
FROM inserted

Let us know if that fixes your problem. But even if it doesn't, you need to change it to what I have.

Tara

littlewing
Starting Member

33 Posts

Posted - 2005-08-22 : 09:28:15

Thanks tduggan that seems to have done the trick. The variable useage works fine in other triggers we have - although not involved with DTS.

Thanks again,
LW

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-22 : 12:23:18

quote:
Originally posted by littlewing

Thanks tduggan that seems to have done the trick. The variable useage works fine in other triggers we have - although not involved with DTS.

Thanks again,
LW

Your inserts must only be affecting one row then if your triggers are working properly. You should never code a trigger with this assumption then. Even if they work right now, they might not in the future. As soon as your insert statement affects more than one row, the trigger will not function as desired.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources