SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with a Trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Dmh188
Starting Member

35 Posts

Posted - 12/21/2011 :  13:43:28  Show Profile  Reply with Quote
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 - 12/21/2011 :  13:53:15  Show Profile  Reply with Quote
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/


Go to Top of Page

Dmh188
Starting Member

35 Posts

Posted - 12/21/2011 :  13:56:57  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/21/2011 :  13:58:15  Show Profile  Reply with Quote
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.
Go to Top of Page

Dmh188
Starting Member

35 Posts

Posted - 12/21/2011 :  14:07:05  Show Profile  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 12/21/2011 :  14:11:56  Show Profile  Reply with Quote
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/


Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 12/21/2011 :  14:13:29  Show Profile  Reply with Quote
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/


Go to Top of Page

Dmh188
Starting Member

35 Posts

Posted - 12/21/2011 :  14:17:35  Show Profile  Reply with Quote
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?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 12/21/2011 :  14:20:32  Show Profile  Reply with Quote
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/



Edited by - X002548 on 12/21/2011 14:23:02
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 12/21/2011 :  14:25:35  Show Profile  Reply with Quote
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/


Go to Top of Page

Dmh188
Starting Member

35 Posts

Posted - 12/21/2011 :  14:30:21  Show Profile  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 12/21/2011 :  14:35:58  Show Profile  Reply with Quote
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/


Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 12/21/2011 :  14:40:23  Show Profile  Reply with Quote
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



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/



Edited by - X002548 on 12/21/2011 14:46:38
Go to Top of Page

Dmh188
Starting Member

35 Posts

Posted - 12/21/2011 :  14:42:59  Show Profile  Reply with Quote
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
Go to Top of Page

Dmh188
Starting Member

35 Posts

Posted - 12/21/2011 :  14:58:56  Show Profile  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 12/21/2011 :  15:36:36  Show Profile  Reply with Quote
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/


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000