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

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

---

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.

---

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