Avatar of PtboGiser
PtboGiser
Flag for Canada asked on

Trigger - RE-write

Create TRIGGER [dbo].[Add_ODD_ROAD_Seg] ON  [dbo].[Segment]
for update 
AS 
if UPDATE(L_F_ADD)
BEGIN

INSERT INTO dbo.Segment_History
select * from inserted 
END

if UPDATE(L_T_ADD)
BEGIN

INSERT INTO dbo.Segment_History
select * from inserted 
END

if UPDATE(R_F_ADD)
BEGIN

INSERT INTO dbo.Segment_History
select * from inserted 

END
if UPDATE(R_T_ADD)
BEGIN

INSERT INTO dbo.Segment_History
select * from inserted 
END

if UPDATE(Left_ODD)
BEGIN

INSERT INTO dbo.Segment_History
select * from inserted 
END

if UPDATE(ROADJUR)
BEGIN

INSERT INTO dbo.Segment_History
select * from inserted 
	
END

Open in new window


I am re building this trigger from a old enviroment into our new work one.
 I'm getting the following error.
Msg 311, Level 16, State 1, Procedure Add_ODD_ROAD_Seg, Line 7
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

It looks to me there is a better way to write the old query.
Can someone help me?
Thanks
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft Development

Avatar of undefined
Last Comment
Anthony Perkins

8/22/2022 - Mon
mlaise

The error is related to the data fields your tables are using.  If you use inserted you can not call forward text, ntext, or image columns in the trigger (which I assume you are doing with the SELECT all command you are using.

You can set the trigger to INSTEAD which allows access to those fields.

Here is a quick walkthrough of how to re-work the trigger: http://www.thetugboatcomplex.com/?p=13
PtboGiser

ASKER
Data Type problem, i see why.

Is there a better way to write the trigger? (disregard the Error i have fixed that issue) Its seems clunky to me?
mlaise

It really depends on what you expect it to do.  Without knowing what you expect it to do it makes it a bit hard for me to modify it.
Your help has saved me hundreds of hours of internet surfing.
fblack61
PtboGiser

ASKER
The Trigger is designed to insert the changed row into the Segment_History table upon an insert, delete or update of the row.
So if any of the values in the above stated columns change insert the record into the Segment_history table.
SOLUTION
Anthony Perkins

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
PtboGiser

ASKER
If one or a couple of the 6 columns are updated just insert the entire record once into the History table.
No need for having the record inserted mutliple times for each change.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PtboGiser

ASKER
so like this then

USE [CNTY_GIS]
GO
/****** Object:  Trigger [dbo].[Segment_Update]    Script Date: 01/04/2013 13:57:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[Segment_Update] 
ON  [dbo].[Segment]
FOR UPDATE 
AS 
IF UPDATE(L_F_ADD) OR UPDATE(L_T_ADD) OR UPDATE(R_T_ADD)OR UPDATE(R_F_ADD) OR UPDATE(L_T_ADD) or UPDATE(ROADJUR)
    SELECT * FROM inserted

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PtboGiser

ASKER
If a change is made on any one of those columns the trigger will fire and send it to the Segment_History table.

I will need a seperate Trigger to deal with deleted\retired segments. I;ll work on that now.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PtboGiser

ASKER
Thanks for your help as always guys. Some of this is starting to make sense!
PtboGiser

ASKER
Yes added the insert. a little early posting back
Thanks again Scott
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Anthony Perkins

If a change is made on any one of those columns the trigger will fire
Actually no, that is a myth.  The UPDATE(ColumnName) does not indicate that the value changed.   In order to find out if the value really changed you have to inspect the values of the INSERTED and DELETED logical tables.
Scott Pletcher

>>
If a change is made on any one of those columns the trigger will fire
Actually no, that is a myth.  The UPDATE(ColumnName) does not indicate that the value changed.  
<<

I state something, you state something entirely different, then state that what I said was a "myth".

I NEVER stated the UPDATE() reflected a true value change on that specific column.  I said the trigger would be fired if any of the columns actually changed in value.

Your claim about what I stated is the only "myth" here.
Anthony Perkins

I NEVER stated the UPDATE() reflected a true value change on that specific column.
Calm down, Scott, I never said you had.  If you look carefully my comment was addressing the author's quote, not yours.

Of course I know that you know full well that UPDATE() has nothing to do with the actual value getting changed.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.