Create triggers - 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 2005 Forums

Transact-SQL (2005)

Create triggers

Author

Topic

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-07 : 16:19:34

Need help. I have created a trigger as follows. To check if the trigger is working I wrote an insert query and I got the following error. I don't see the [dbo].[int_upc_ean_audit_trigger] table either in the database.

Error
==================
Msg 208, Level 16, State 1, Procedure int_upc_ean_audit_trigger, Line 13
Invalid object name 'int_upc_ean_audit'.

Trigger code:
======================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create TRIGGER [dbo].[int_upc_ean_audit_trigger]
ON [dbo].[int_upc_ean]
FOR INSERT, UPDATE
AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF (SELECT COUNT(*) FROM inserted) > 0
BEGIN
-- update!
INSERT int_upc_ean_audit
(frcst_altr_id, upc, ean_cd,operation, upperIDSID, change_date)
SELECT frcst_altr_id, upc, ean_cd,'U', system_user, getdate()
FROM inserted
END
ELSE
BEGIN
-- insert!
INSERT into int_upc_ean_audit
(frcst_altr_id, upc, ean_cd,operation, upperIDSID,change_date)
SELECT frcst_altr_id, upc, ean_cd,'I', system_user, getdate()
FROM inserted

END

SA

SA

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-07 : 16:52:59

1. a trigger isn't a table. Expand tables, then triggers in the object explorer. perhaps u created trigger in wrong database?

2. there's a logic flaw in your trigger -- count(*) from inserted will be > 0 for both updates and inserts.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-07 : 17:02:39

or select * from sys.triggers

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-07 : 17:04:30

Thanks for the response. I need some clarifications

Regarding 1:
Yes I see the trigger but I am also using "insert into" so I need to create that table .. Right!

Regarding 2:
How do I fix the design flaw. Some more info on that:
frcst_alt_id field can get new inserts only. So if it does not exists we let the users enter a brand new id into this field.
upc,ean_cd are the only fields that get assigned/updated if they are empty at the users request.

SA

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-07 : 17:14:39

TG answered that in your other post

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130847

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources