Posted by teddysnips on 03/17/06 16:24

I need a trigger (well, I don't *need* one, but it would be optimal!)
but I can't get it to work because it references ntext fields.

Is there any alternative? I could write it in laborious code in the
application, but I'd rather not!

DDL for table and trigger below.

TIA

Edward

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblMyTable]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblMyTable]
GO

CREATE TABLE [dbo].[tblMyTable] (
[fldCSID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[fldSubject][ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldKBSubject] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldKBDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TRIGGER PrepopulateKBFieldsFromQuery ON dbo.tblMyTable

FOR INSERT

AS

BEGIN


IF UPDATE(fldKBSubject)
BEGIN

UPDATE
tblMyTable
SET
fldSubject = i.fldKBSubject
FROM
inserted i INNER JOIN
tblMyTable ON i.fldCSID = tblMyTable.fldCSID

END

IF UPDATE (fldKBDescription)
BEGIN
UPDATE
tblMyTable
SET
fldDescription = i.fldKBDescription
FROM
inserted i INNER JOIN
tblMyTable ON i.fldCSID = tblMyTable.fldCSID
END
END

[Back to original message]