Trigger Problem - SQL Server Forums

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)

Trigger Problem

Author

Topic

gertbelien
Starting Member

9 Posts

Posted - 2006-02-10 : 05:11:23

I tried to create the following :

CREATE TRIGGER Create_Email_Customer ON [dbo].[CL_CALL_LOGGING]
FOR INSERT
AS

declare
@sProblem_Desc VARCHAR(2000),
@sSolution_Desc VARCHAR(2000),
@sResult_Desc VARCHAR(2000)

BEGIN
SELECT
@sProblem_Desc = CONVERT(VARCHAR(2000),PROBLEM_DESC),
@sSolution_Desc = CONVERT(VARCHAR(2000),SOLUTION_DESC)
FROM inserted

IF SUBSTRING (@sProblem_Desc, 1, 22) = '[Call Logged via Email'
IF charindex(char(13), @sSolution_Desc) > 0
select @sResult_Desc = right(@sSolution_Desc, charindex(char(13), reverse(@sSolution_Desc))-2) + @sProblem_Desc
else select @sResult_Desc = @sSolution_Desc + @sProblem_Desc

update CL_CALL_LOGGING
SET PROBLEM_DESC = @sResult_Desc
WHERE CALL_NUMBER = i.CALL_NUMBER
END

When i execute this i'll get the following error :
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
The column prefix 'i' does not match with a table name or alias name used in the query.

ANY IDEA's ???? (i'm a newbie in SQL SERVER, oracle geak)

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-10 : 05:24:31

Hi,
This is because the Magic Table (Inserted or deleted )does not contain the information about the columns of the data-type text, ntext, or image. however, these column references are allowed for INSTEAD OF triggers.

for more info refer this..
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_0lo3.asp

However the last error is because you have missed [from Inserted i] in the update statement

update CL_CALL_LOGGING
SET CL_CALL_LOGGING.PROBLEM_DESC = @sResult_Desc
from Inserted i
WHERE CL_CALL_LOGGING.CALL_NUMBER = i.CALL_NUMBER
END

gertbelien
Starting Member

9 Posts

Posted - 2006-02-10 : 06:04:47

Thanks !

I'm very new in SQL SERVER.
Any idea how i should change my trigger ?
The goal is the following :
Whenever a record is inserted into CL_CALL_LOgging
and the Problem_Desc value starts with '[Call Logged via Email'
i need the last line of the Text field Solution_Desc and i have to
add the line in Problem_Desc(also a text field) as firstline

Kristen
Test

22859 Posts

Posted - 2006-02-10 : 06:14:31

Continued from this thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61500

Kristen
Test

22859 Posts

Posted - 2006-02-10 : 06:16:14

"Any idea how i should change my trigger"

You need to change it from

FOR INSERT

to

INSTEAD OF INSERT

and you will also need to make the INSERT into CL_CALL_LOGGING within your revised trigger (because its an INSTEAD OF trigger it will NOT do the actual Insert for you, so you need to do that - note that doing the Insert in the Instead Of trigger will NOT cause the trigger to be called recursively!)

Kristen

gertbelien
Starting Member

9 Posts

Posted - 2006-02-10 : 06:26:18

Thanks Kristen,

Would you be so kind to help me with this one ?
Do i use the inserted table to do the actual insert ?
I know it seems stupid but i'm completely new in SQL SERVER

Kristen
Test

22859 Posts

Posted - 2006-02-10 : 06:46:26

"Do i use the inserted table to do the actual insert ?"

Yup, that's exactly how you would do it.

Of course you could choose NOT to insert all the rows / columns, or even to insert them to different tables, that's certainly a possible use of the INSTEAD OF trigger, but in your case you just want to:

1) Insert the row into the underlying table
2) Process part of it into another table.

Ordinarily you could do this with a normal AFTER trigger, but AFTER triggers do not preserve any "large" TEXT/IMAGE column data, and in this case you do need to have access to that.

An alternative would be to use an AFTER trigger and join the INSERTED table back to the underlying table which could then be used to get you the latest values in the TEXT column you need.

Thinking about it this might be better - the real nuisance with an INSTEAD OF trigger is if you add columns to the table you need to remember to add them to the INSTEAD OF trigger too - otherwise your INSERTS are only going to insert the original columns, and not the new ones!

Kristen

gertbelien
Starting Member

9 Posts

Posted - 2006-02-10 : 10:23:42

Kristen,

I finally found the solution !!!
Thanks for all the help

this is the result :

CREATE TRIGGER Save_Email_Subject ON [dbo].[CL_CALL_LOGGING]
AFTER INSERT
AS

declare
@sProblem_Desc VARCHAR(2000),
@sSolution_Desc VARCHAR(2000),
@sResult_Desc VARCHAR(2000)

BEGIN
SELECT
@sProblem_Desc = CONVERT(VARCHAR(2000),c.PROBLEM_DESC),
@sSolution_Desc = CONVERT(VARCHAR(2000),c.SOLUTION_DESC)
FROM CL_CALL_LOGGING c, inserted i
WHERE C.CALL_NUMBER = i.CALL_NUMBER

IF SUBSTRING (@sProblem_Desc, 1, 22) = '[Call Logged via Email'
IF charindex(char(10), @sSolution_Desc) > 0
select @sResult_Desc = right(@sSolution_Desc, charindex(char(10), reverse(@sSolution_Desc))-1) + char(13) +char(10) + @sProblem_Desc
else select @sResult_Desc = @sSolution_Desc + char(13) +char(10) + @sProblem_Desc

update CL_CALL_LOGGING
SET CL_CALL_LOGGING.PROBLEM_DESC = @sResult_Desc
FROM inserted i
WHERE CL_CALL_LOGGING.CALL_NUMBER = i.CALL_NUMBER

END

Kristen
Test

22859 Posts

Posted - 2006-02-10 : 13:13:55

"I finally found the solution"

Excellent! Well done!

Note that your solution will only work for single-row-insert.

If there are multiple rows in the "inserted" table then:

SELECT
@sProblem_Desc = CONVERT(VARCHAR(2000),c.PROBLEM_DESC),
@sSolution_Desc = CONVERT(VARCHAR(2000),c.SOLUTION_DESC)
FROM CL_CALL_LOGGING c, inserted i
WHERE C.CALL_NUMBER = i.CALL_NUMBER

will pick an unpredictable value to store in your @variables.

You could do all that processing "in one strike" in your final UPDATE instead, which would allow the trigger to handle multi-row inserts. I haven't checked this, so its probably chock full of error etc., but it may give you an idea. You may also need to CAST/CONVERT the TEXT columns to varchar - not sure whether some of these functions can operate on TEXT or not.


update CL_CALL_LOGGING
SET CL_CALL_LOGGING.PROBLEM_DESC = 
    CASE WHEN charindex(char(10), @sSolution_Desc) > 0
              THEN right(c.SOLUTION_DESC, charindex(char(10), reverse(c.SOLUTION_DESC))-1)
                   + char(13) + char(10) + c.PROBLEM_DESC
              ELSE c.SOLUTION_DESC+ char(13) +char(10) + c.PROBLEM_DESC
              END
FROM inserted AS i
     JOIN CL_CALL_LOGGING AS c
         ON c.CALL_NUMBER = i.CALL_NUMBER
WHERE SUBSTRING(c.PROBLEM_DESC, 1, 22) = '[Call Logged via Email'

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources