CREATE TRIGGER for insert not working with ntext

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

SQL Server 2000 Forums

SQL Server Development (2000)

CREATE TRIGGER for insert not working with ntext

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2004-12-07 : 15:32:32

I want to use a create trigger for insert and update.

But when i use a field comment which is ntext datatype.

I am getting the error:

Error: 311
Cannot use ntext, text, image columns in the inserted and deleted tables.

Following is my Create Trigger procedure in sql server, Can you please help, how to use the comment field also in the following trigger which is ntext datatype.

*************
CREATE TRIGGER insTAB_ccsNetActions_log ON dbo.TAB_ccsNetActions
FOR INSERT, UPDATE
AS

DECLARE @TimeStamp datetime

Set @TimeStamp = GetDate()

INSERT INTO TAB_ccsNetActions_log
(ModuleRecordID,
ModuleName,
ActionID,
ActionNO,
Critical,
ActionCode,
AssignedTo,
Company,
DueDate,
CompleteDate,
ActionDescription,
Status,
Comment,
Deleted,
Updated,
UpdatedBy)
SELECT
ins.ModuleRecordID,
ins.ModuleName,
ins.ActionID,
ins.ActionNO,
ins.Critical,
ins.ActionCode,
ins.AssignedTo,
ins.Company,
ins.DueDate,
ins.CompleteDate,
ins.ActionDescription,
ins.Status,
ins.Comment,
ins.Deleted,
@TimeStamp,
'SQLServer'
FROM inserted ins

********************************

Thank you very much.
***********

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-12-07 : 15:37:59

Maybe something like:

INSERT INTO TAB_ccsNetActions_log
(
	ModuleRecordID,
	ModuleName,
	ActionID,
	ActionNO,
	Critical,
	ActionCode,
	AssignedTo,
	Company,
	DueDate,
	CompleteDate,
	ActionDescription,
	Status,
	Comment,
	Deleted,
	Updated,
	UpdatedBy
)
SELECT
	ins.ModuleRecordID,
	na.ModuleName,
	na.ActionID,
	na.ActionNO,
	na.Critical,
	na.ActionCode,
	na.AssignedTo,
	na.Company,
	na.DueDate,
	na.CompleteDate,
	na.ActionDescription,
	na.Status,
	na.Comment,
	na.Deleted,
	@TimeStamp,
	'SQLServer'

FROM 	inserted ins 
JOIN	dbo.TAB_ccsNetActions na ON na.ModuleRecordID = ins.ModuleRecordID

reddymade
Posting Yak Master

165 Posts

Posted - 2004-12-07 : 15:55:07

Ehorn, You are the MAN.

It is working Great, Thank you very much.

quote:

Originally posted by ehorn

Maybe something like:

INSERT INTO TAB_ccsNetActions_log
(
	ModuleRecordID,
	ModuleName,
	ActionID,
	ActionNO,
	Critical,
	ActionCode,
	AssignedTo,
	Company,
	DueDate,
	CompleteDate,
	ActionDescription,
	Status,
	Comment,
	Deleted,
	Updated,
	UpdatedBy
)
SELECT
	ins.ModuleRecordID,
	na.ModuleName,
	na.ActionID,
	na.ActionNO,
	na.Critical,
	na.ActionCode,
	na.AssignedTo,
	na.Company,
	na.DueDate,
	na.CompleteDate,
	na.ActionDescription,
	na.Status,
	na.Comment,
	na.Deleted,
	@TimeStamp,
	'SQLServer'

FROM 	inserted ins 
JOIN	dbo.TAB_ccsNetActions na ON na.ModuleRecordID = ins.ModuleRecordID

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-12-07 : 16:18:40

Glad that worked for you. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources