Help with a 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

Help with a Trigger

Author

Topic

Dmh188
Starting Member

37 Posts

Posted - 2011-12-21 : 13:43:28

Hey guys,

I am creating a trigger but something is going wrong. Basically i need a table to be updated with information that another table is updated with.

The table the trigger is attached to is do_tracking_test (im running on a test db)
Its columns are PackageNo and Tracking (both are text fields for testing phase so you will see some converts in the trigger)

It will then update table PO. more specifically it is updating the column of MDESC. This column contains note specific to this DO. The reference field on this table is use to join the PO table to the do_tracking_table. This is what i have

CREATE TRIGGER DO_Tracking_ya ON do_tracking_test AFTER UPDATE
as
begin
update Po
set PO.mdesc = convert(varchar(max), po.mdesc)+ '

'+ convert(varchar(max), DO_Tracking_test.packageno) +''+
convert(varchar(max), DO_Tracking_test.tracking)
from po, do_tracking_test
where left(do_tracking_test.packageno,5) = PO.reference
and PO.status = 5
end

Now the left(do_tracking_test.packageno,5) is this way because it is being written as v1111-1. v1111 is the DO number and the -1 is the package number. so there could be v1111-1, v1111-2, v1111-3 etc etc. I trigger works great. It runs when the table is updated, it then goes to the PO table and pretty much copies what information is there, and then added the package number and tracking number to the notes. The problem is works great with v1111-1, but when v1111-2 is introduced, it triggers, copies the data, but then just reenters the data from v1111-1 and from v1111-2 or v1111-3 etc..

I hope i did a good job of describing the problem. How would i go about getting it to update with just the newly added row of information (packageno and trackingno)

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-21 : 13:53:15

do you know what the inserted and deleted tables are?

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/

Dmh188
Starting Member

37 Posts

Posted - 2011-12-21 : 13:56:57

Forgive me ignorance but i am not sure what you are talking about. There for sure isn't a table deleted. do_tracking_Test is the only table that i guess you could say gets an insert. The packageno and tracking info from our shipping program is put into that table, then onces that is done, the trigger happens

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-21 : 13:58:15

Not completely clear to me, but it seems like you will be updating all the rows in the PO table each time the trigger fires. You probably want to update ONLY the rows that were updated. If so, change it like this:

CREATE TRIGGER DO_Tracking_ya ON do_tracking_test AFTER UPDATE
as 
begin
update Po
set PO.mdesc = convert(varchar(max), po.mdesc)+ '

'+ convert(varchar(max), INSERTED.packageno) +''+
convert(varchar(max), INSERTED.tracking)
from po, INSERTED
where left(INSERTED.packageno,5) = PO.reference
and PO.status = 5
end

The trigger code has access to two virtual tables - INSERTED and DELETED. INSERTED contains the data after the update, and DELETED contains the data before the update.

Dmh188
Starting Member

37 Posts

Posted - 2011-12-21 : 14:07:05

Sunitabeck,

This is throwing the following error :

Msg 311, Level 16, State 1, Procedure DO_Tracking_ya, Line 4
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

The column MDESC in PO is a text field, and i can not change it to anything else in the db

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-21 : 14:11:56

inserted and deleted are sql server virtual tables

inserted contains all the information from an insert statement, and new information about an update statement

deleted are the old values of an update and the values of a delete statement

they are only available in a trigger

they only contain the values of a DML Statement

These are probably the values you want

why sun is not writing in ANSI I don't know

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/

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-21 : 14:13:29

quote:
Originally posted by Dmh188

Sunitabeck,

This is throwing the following error :

Msg 311, Level 16, State 1, Procedure DO_Tracking_ya, Line 4
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

The column MDESC in PO is a text field, and i can not change it to anything else in the db

and that means exactly what it says..just curious as to why your column is defined as text and not varchar???

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/

Dmh188
Starting Member

37 Posts

Posted - 2011-12-21 : 14:17:35

Its the how it came with the software. The db is for Everest Advanced Edition. basically our accounting software. do you know of a way around this problem?

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-21 : 14:20:32

~~maybe you can do a convert to varchar~~

I see said the blind man....

can you post the DDL of the 2 tables?

or can you at least tell us which column is text..damn 3rd party vendor "applications" scrubs..probably no stored procedures right?

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/

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-21 : 14:25:35

from books online

quote:

SQL Server allows for the update of text, ntext, or image columns through the INSTEAD OF trigger on tables or views.

Important:
ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. Both AFTER and INSTEAD OF triggers support varchar(MAX), nvarchar(MAX), and varbinary(MAX) data in the inserted and deleted tables.

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/

Dmh188
Starting Member

37 Posts

Posted - 2011-12-21 : 14:30:21

do_tracking_Test( packageno text, tracking text)

PO(MDESC text, Reference varchar(40))

here is how it suppose to work step by step.
do_tracking_test has a packageno and tracking number entered into it when the package is shipped. so it should be added like
v1111-1 123
v1111-2 234
v1111-3 567
v2222-1 1234
v2222-2 5678
When a row is added to that table the trigger is then suppose to go to PO, it looks at the Reference column. from do_tracking_test it takes, for example, v1111-1 and using LEFT, will look at just v1111, it takes v1111 and looks at PO.Reference for a match.
Once it finds it, it then updates PO.MDESC. It will copy what ever is already there, then add the packageno and tracking. MDESC is basically notes associated with this particular DO(debit ORDER). so it would look like:

notes for v1111 blah blah blah.

it would then basically copy that information and during the update will put it back in and then add package no and tracking, so end result would be, if we shipped v1111-1:
notes for v1111 blah blah blah. v1111-1 123
And then if we shipped package v1111-2. it should look like:
Notes for v1111 blah blah blah. v1111-1 123 v1111-2 345

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-21 : 14:35:58

ok, but why would you just use the inserted table that has what's just been inserted?

And as you have found out you can't use a text column in a trigger with the inserted table

How does the original insert occur?

Through a sproc or dynamically by code?

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/

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-21 : 14:40:23

not sure if I am thrilled with what you are doing...denormalizing data

but what about this


CREATE TRIGGER DO_Tracking_ya ON do_tracking_test AFTER UPDATE
AS
	BEGIN
		UPDATE p
		   SET mdesc = convert(varchar(max), p.mdesc)+ ''
						+ convert(varchar(max), d.packageno) +''
						+ convert(varchar(max), d.tracking)
		 FROM po p
   INNER JOIN do_tracking_test d
		   ON left(d.packageno,5) = p.reference
		  AND p.status = 5
	END

Dmh188
Starting Member

37 Posts

Posted - 2011-12-21 : 14:42:59

the original insert hasnt happened yet. Fedex has to come out and update their coding. Right now it is just happening by my writing an insert statement. It works just fine when im only dealing with v1111-1. It rewrites everything perfectly, but when v1111-2 is introduced it just puts the info in from v1111-1.

My boss wanted it to be written to another table before it was written to PO

Dmh188
Starting Member

37 Posts

Posted - 2011-12-21 : 14:58:56

Brett,

I am updating v1111-2 (packageno) with a new tracking number. After that i checked the PO table and again it just added the information from v1111-1

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-21 : 15:36:36

ok

can you just write a select that does what you want? Outside of a trigger (which I guess is firing now?)

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources