Issue with After Update Trigger

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)

Issue with After Update Trigger

Author

Topic

rmrper99
Starting Member

29 Posts

Posted - 2010-11-12 : 11:23:25

I am having a problem with the following trigger. When it fires, it inserts 2 records into the new table. The 1st record is the data before the update and the 2nd record is the data after the update. I just want the data after the update. Any help would be greatly appreciated.

AFTER UPDATE
AS
BEGIN
INSERT INTO [HHG].[dbo].[SalesPrice_History]
([Add3],[ClassId],[CustID],[DiscOnly],[DiscPct])
SELECT
[Add3],[ClassId],[CustID],[DiscOnly],[DiscPct]
FROM INSERTED
END

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-12 : 11:26:38

i HAVE AN IDEA...BUT POT THE WHOLE TRIGGER

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

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

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

rmrper99
Starting Member

29 Posts

Posted - 2010-11-12 : 11:29:52

Ok, here is the whole trigger. I just excluded some of the additional fields from the original sample. Not sure why that matters.
AFTER UPDATE
AS
BEGIN
INSERT INTO [HHG].[dbo].[MSCSPSalesPrice_History]
([Add3],[ClassId],[CustID],[DiscOnly],[DiscPct],
[EndDate],[FromQty],[InvtID],[LineRef],[MinPrice],[Note],
[PriceClassId],[RecordDate],[StartDate],[ToQty],[UnitPrice],[UOM],
[User1],[User2],[User3],[User4],[User5],[User6],[User7],[User8])
SELECT
[Add3],[ClassId],[CustID],[DiscOnly],[DiscPct],
[EndDate],[FromQty],[InvtID],[LineRef],[MinPrice],'Existing Record Updated',
[PriceClassId],GetDate(),[StartDate],[ToQty],[UnitPrice],[UOM],
[User1],[User2],[User3],[User4],[User5],[User6],[User7],[User8]
FROM INSERTED
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-12 : 11:40:26

your trigger looks ok. Are you sure you're not having any other additional triggers on same table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-12 : 11:45:28

how about the create trigger statement

my guess is it's on the same table

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

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

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

rmrper99
Starting Member

29 Posts

Posted - 2010-11-12 : 11:56:18

visakh16 - Unfortunately I don't have any additional triggers on this table.

I'm sorry Brett, but I am not sure what you are looking for. This is the "Create Trigger Statement". I just excluded the words from the beginning of the post because they don't apply.

There is only 1 trigger on this table and the entire trigger is posted above. When I update a record in the MSCSPSALESPRICE table, 2 records are inserted into the MSCSPSALESPRICE_HISTORY table. One record from the way it was before the update and one record the way it was after the update. I would like to only have the record from after the update as I already have the original record.

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-12 : 12:00:47

How are you doing the upate? Through a stored procedure?

rmrper99
Starting Member

29 Posts

Posted - 2010-11-12 : 12:04:01

The update is actually happening through the front end vb screen. When the user makes a change and saves the change the record is then updated in the table.

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-12 : 12:04:34

WHAT Table is the trigger on?

MSCSPSALESPRICE

????

CREATE TRIGGER <trigger_name> ON MSCSPSALESPRICE
AFTER UPDATE
AS
BEGIN
INSERT INTO [HHG].[dbo].[MSCSPSalesPrice_History]
([Add3],[ClassId],[CustID],[DiscOnly],[DiscPct],
[EndDate],[FromQty],[InvtID],[LineRef],[MinPrice],[Note],
[PriceClassId],[RecordDate],[StartDate],[ToQty],[UnitPrice],[UOM],
[User1],[User2],[User3],[User4],[User5],[User6],[User7],[User8])
SELECT
[Add3],[ClassId],[CustID],[DiscOnly],[DiscPct],
[EndDate],[FromQty],[InvtID],[LineRef],[MinPrice],'Existing Record Updated',
[PriceClassId],GetDate(),[StartDate],[ToQty],[UnitPrice],[UOM],
[User1],[User2],[User3],[User4],[User5],[User6],[User7],[User8]
FROM INSERTED
END

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

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

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

rmrper99
Starting Member

29 Posts

Posted - 2010-11-12 : 12:06:57

Yes, that is correct.

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-12 : 12:09:21

maybe you can show us the update?

Also

do you know how to script DDL

and what version are you using?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

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

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-12 : 12:13:35

I would also consider looking at the front end code as well to see if it's doing something.

rmrper99
Starting Member

29 Posts

Posted - 2010-11-12 : 12:15:24

The update happens through the front end so I am not sure how to show you that. I know a little about DDL but not a ton. I am using 2005.

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-12 : 12:18:29

So, the front end (what is the front end?) builds the SQL command and then executes it, yes? Look at the code that the front end is generating.

rmrper99
Starting Member

29 Posts

Posted - 2010-11-12 : 12:18:49

Would it have anything to do with the fact that the history table has an identity column. That field isn't included in my statement above because I am not inserting into that field.

rmrper99
Starting Member

29 Posts

Posted - 2010-11-12 : 12:22:04

Thanks. I'll take a look at the front end.

mandm
Posting Yak Master

120 Posts

Posted - 2010-11-12 : 16:07:01

Okay this could be a really stupid question. The trigger is processing an update, but you are inserting a new row into your history table. Could the duplication be due to multiple updates?

You may want to have the trigger check to see if the row already exists in the history table then do either an insert or update accordingly.

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-12 : 16:41:28

not to be mean

But there are no SQL miracles

Since you are saying 2 rows are being inserted...and from (the limited) info we have, only 3 things can be happening

There is another trigger

There more to this trigger

Or the update is updating the row twice

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

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

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-12 : 16:43:44

quote:
Originally posted by X002548

But there are no SQL miracles

Are you saying there is no SQL claus(e)?

Kristen
Test

22859 Posts

Posted - 2010-11-13 : 05:27:54

"Would it have anything to do with the fact that the history table has an identity column"

No

Long-shot:

Is the UPDATE updating the row twice?


UPDATE U
SET MyCol = 'NewValue'
FROM MyTable AS U
    JOIN MyOtherTable AS O
        ON O.ID = U.ID

Dunno what happens in this scenario if MyOtherTable has two matching rows for the single row in MyTable - perhaps it updates it twice - and thus appears twice in the INSERTED pseudo-table in the trigger?

Kristen
Test

22859 Posts

Posted - 2010-11-13 : 05:30:27

P.S. Suggest you add an "AuditDate" column to MSCSPSalesPrice_History and populate it with GetDate() - that will tell you if the two rows inserted are from the same UPDATE or not (obviously two separate Updates COULD have the same millisecond-time, but its unlikely that all near-same-time ones will have. Two in the same INSERT statement WILL have the same time, always.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources