insert 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

insert update Trigger

Author

Topic

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-07 : 16:17:43

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-07 : 17:00:44

Did you try to insert into the trigger or the table

the table name is: int_upc_ean
not: int_upc_ean_audit_trigger

Also, your update condition should be checking the [deleted] table not the [inserted] table.

You can actually just use one statement. [inserted] i left outer join [deleted] d on d.<PK> = i.<PK>
then for operation use a case statement: case when d.<pk> is not null then 'U' else 'I' end

Be One with the Optimizer
TG

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-07 : 17:09:37

so, your update condition should be checking the [deleted] table not the [inserted] table.

Can u explain this further.Why are we checking the [deleted] table. I am only needing insert and updates to be the triggers.

SA

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-07 : 17:11:20

for an update action you will have the old values in [deleted] and the new values in [inserted]. for an insert action [deleted] will be empty.

EDIT:
and, of course, if the trigger was also after DELETE then for a delete action the [inserted] table would be empty and the [deleted] table would have the deleted row(s)

Be One with the Optimizer
TG

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-07 : 17:18:19

Perrrrfect! Thanks for resolving this issue for me.

SA

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-07 : 17:33:41

Well I fixed the logic now bit I am still getting an error when I run my insert query.

Error:
=========================
Msg 8152, Level 16, State 2, Procedure int_upc_ean_audit_trigger, Line 22
String or binary data would be truncated.
The statement has been terminated.

Updated Trigger Code:
=====================
USE [spark]
GO
/****** Object: Trigger [dbo].[int_upc_ean_audit_trigger] Script Date: 08/07/2009 14:10:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[int_upc_ean_audit_trigger]
ON [dbo].[int_upc_ean]
FOR INSERT, UPDATE
AS
IF (SELECT COUNT(*) FROM inserted) > 0
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF (SELECT COUNT(*) FROM deleted) > 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

END

SA

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-07 : 17:43:17

Check the column datatype definitions between [int_upc_ean] and [int_upc_ean_audit]

They should match. Your audit table will just have an extra column or two (operation and change_date)

what is the primary key of [int_upc_ean] ?

EDIT:
or perhaps it is your [upperIDSID] column isn't big enough for the values returned by system_user.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-07 : 17:49:54

You can simplify this code somewhat (as I mentioned above) with this. This assumes the primary key of [int_upc_ean] is [frcst_altr_id]


ALTER TRIGGER [dbo].[int_upc_ean_audit_trigger]
ON [dbo].[int_upc_ean]
FOR INSERT, UPDATE
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;

       insert int_upc_ean_audit
              (frcst_altr_id
              ,upc
              ,ean_cd
              ,operation
              ,upperIDSID
              ,change_date)
       select i.frcst_altr_id
              ,i.upc
              ,i.ean_cd
              ,case when d.[frcst_altr_id] is null then 'I' else 'U' end
              ,system_user
              ,getdate()
       from   [inserted] i
       left outer join [deleted] d
              on d.[frcst_altr_id] = i.[frcst_altr_id]
END

Be One with the Optimizer
TG

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-07 : 17:59:34

Yes I do need to change my code. But you are right the value returned by system_user was too large. Thank you very very much for helping me.
Also, what does
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SA

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-07 : 18:01:35

Sorry I submitted it too soon. I just wanted to know what this means - set NOCOUNT

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

Thanks again,

SA

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-07 : 18:01:43

Sorry I submitted it too soon. I just wanted to know what this means - set NOCOUNT

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

Thanks again,

SA

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-07 : 18:45:18

http://msdn.microsoft.com/en-us/library/ms189837.aspx

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-07 : 19:45:36

Thank you Russell

SA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources