|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
nText,Text,Image field problem for Update/Delete TriggerHi all,
I am writing a update/delete trigger for some tables. But i am faced with a problem for the tables that have text/ntext or Image kind of columns. Please let me know how this could be solved. regards, Joy Hello Joy,
Could you be a bit more specific? Can you not figure how to catalog the trigger or is there something within the trigger's code that you can't figure out? Thanks, Kent Tegels, DevelopMentor http://staff.develop.com/ktegels/ Dear Kent,
Well, there is no problem for me in writing the trigger code and the triggers seem to work fine for all the other tables except the ones that have text/ntext or image kind of columns. For these kind of tables it says that "Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables." Urgent help solicited, please do provide me with links(if possible) that deal with this issue in detail. For your information i am using SQL 2005. regards, Joy Show quote "Kent Tegels" wrote: > Hello Joy, > > Could you be a bit more specific? Can you not figure how to catalog the trigger > or is there something within the trigger's code that you can't figure out? > > Thanks, > Kent Tegels, DevelopMentor > http://staff.develop.com/ktegels/ > > > Hello Joy,
Ok, that's what I was dreading. Best fix is update the table definition from text to varchar(max). You're going to have to do it eventually anyway. Thanks, Kent Dear Kent,
I cannot change or do anything with the table definition because the business proposition is such. So please suggest me some work around that doesn't need me to alter the table definition because in any case i cannot do that. regards, Joy Show quote "Kent Tegels" wrote: > Hello Joy, > > Ok, that's what I was dreading. Best fix is update the table definition from > text to varchar(max). You're going to have to do it eventually anyway. > > Thanks, > Kent > > > Joy,
You have two options: 1. Query the original table in your trigger to access the text/ntext columns. Join the inserted/deleted tables with the original table via the primary key, and you should be able to do what you need. 2. Use an INSTEAD OF trigger. At least according to Books Online, you can access the ntext/text columns in an INSTEAD OF trigger. Steve Kass Drew University www.stevekass.com Joy wrote: Show quote >Dear Kent, >I cannot change or do anything with the table definition because the >business proposition is such. So please suggest me some work around that >doesn't need me to alter the table definition because in any case i cannot do >that. > >regards, >Joy > >"Kent Tegels" wrote: > > > >>Hello Joy, >> >>Ok, that's what I was dreading. Best fix is update the table definition from >>text to varchar(max). You're going to have to do it eventually anyway. >> >>Thanks, >>Kent >> >> >> >> >> Dear Steve,
Thanks for a prompt and precise reply. Well, i had already adopted the 2nd approach i.e. to use a Instead of Trigger. Now i am faced with a problem where the Insert trigger is not able to insert explicitly in Timestamp type column. Please let me know what could be done in this case. regards, Joy Show quote "Steve Kass" wrote: > Joy, > > You have two options: > > 1. Query the original table in your trigger to access the text/ntext > columns. > Join the inserted/deleted tables with the original table via the primary > key, > and you should be able to do what you need. > > 2. Use an INSTEAD OF trigger. At least according to Books Online, you > can access the ntext/text columns in an INSTEAD OF trigger. > > Steve Kass > Drew University > www.stevekass.com > > > Joy wrote: > > >Dear Kent, > >I cannot change or do anything with the table definition because the > >business proposition is such. So please suggest me some work around that > >doesn't need me to alter the table definition because in any case i cannot do > >that. > > > >regards, > >Joy > > > >"Kent Tegels" wrote: > > > > > > > >>Hello Joy, > >> > >>Ok, that's what I was dreading. Best fix is update the table definition from > >>text to varchar(max). You're going to have to do it eventually anyway. > >> > >>Thanks, > >>Kent > >> > >> > >> > >> > >> > Joy (J**@discussions.microsoft.com) writes:
> Thanks for a prompt and precise reply. Well, i had already adopted the 2nd Ignore the column. The timestamp values is not likely to be set until> approach i.e. to use a Instead of Trigger. > > Now i am faced with a problem where the Insert trigger is not able to > insert explicitly in Timestamp type column. > > Please let me know what could be done in this case. you perform the real insert anyway. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hello Joy,
You'll have to join back on the base table to get those values. Something like this: use scratch go drop table dbo.t1 go create table dbo.t1(id tinyint identity(1,1) primary key,t text) go create trigger dbo.trt1 on dbo.t1 for insert,update,delete as begin update b set b.t='z' from inserted i join dbo.t1 b on i.id=b.id end go insert into dbo.t1 values('x') go select * from dbo.t1 go Again, its only a matter of time before TEXT, NTEXT and IMAGE aren't supported in a future version of SQL Server. Change this as soon as possible. :) Thanks, Kent Tegels, DevelopMentor http://staff.develop.com/ktegels/ Dear Kent,
What if the table doesn't has a primary key? regards, Joy Show quote "Kent Tegels" wrote: > Hello Joy, > > You'll have to join back on the base table to get those values. Something > like this: > > use scratch > go > drop table dbo.t1 > go > create table dbo.t1(id tinyint identity(1,1) primary key,t text) > go > create trigger dbo.trt1 on dbo.t1 for insert,update,delete as begin > update b set b.t='z' from inserted i join dbo.t1 b on i.id=b.id > end > go > insert into dbo.t1 values('x') > go > select * from dbo.t1 > go > > Again, its only a matter of time before TEXT, NTEXT and IMAGE aren't supported > in a future version of SQL Server. Change this as soon as possible. :) > > Thanks, > Kent Tegels, DevelopMentor > http://staff.develop.com/ktegels/ > > > Joy (J**@discussions.microsoft.com) writes:
> Dear Kent, All tables should have a primary key. If a table does not have a > What if the table doesn't has a primary key? primary key, then it's very difficult to work with it anyway. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >> What if the table doesn't has a primary key? While I agree with Erland, as long as a row has an identity or row GUID, > All tables should have a primary key. If a table does not have a > primary key, then it's very difficult to work with it anyway. you can join on that, albeit less efficiently. Thanks, Kent Tegels, DevelopMentor http://staff.develop.com/ktegels/ Kent Tegels (kteg***@develop.com) writes:
>>> What if the table doesn't has a primary key? But in that case, the table has a primary key, although it is not defined>> All tables should have a primary key. If a table does not have a >> primary key, then it's very difficult to work with it anyway. > > While I agree with Erland, as long as a row has an identity or row GUID, > you can join on that, albeit less efficiently. as such. (And many would argue that it's a primary key that does not count, but at least for operations like the one we are discussing it's perfectly sufficient.) -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Joy (J**@discussions.microsoft.com) writes:
> I cannot change or do anything with the table definition because the Beware that varchar(MAX) can handle the same amount of data as text,> business proposition is such. So please suggest me some work around that > doesn't need me to alter the table definition because in any case i > cannot do that. but is a lot easier to work with, since all restrictions that comes with the text data type are not there. True, the old operations READTEXT, WRITETEXT etc do not work with varchar(MAX), so if you are using these, you will have to modify your code. There is all reason to give a second thought to change the data type. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics