Solved

Trigger - RE-write

Posted on 2013-01-02
15
271 Views
Last Modified: 2013-01-04
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
0
Comment
Question by:PtboGiser
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 3
  • +1
15 Comments
 
LVL 5

Expert Comment

by:mlaise
ID: 38737668
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
0
 

Author Comment

by:PtboGiser
ID: 38737736
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?
0
 
LVL 5

Expert Comment

by:mlaise
ID: 38737799
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.
0
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 

Author Comment

by:PtboGiser
ID: 38740221
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.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 166 total points
ID: 38742551
So if all 6 columns are involved in an UPDATE statement you would like to see the same row inserted 6 times in the Segment_History table?
0
 

Author Comment

by:PtboGiser
ID: 38744505
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.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 334 total points
ID: 38744558
CREATE TRIGGER [dbo].[Add_ODD_ROAD_Seg]
ON  [dbo].[Segment]
FOR UPDATE
AS
INSERT INTO dbo.Segment_History
SELECT * FROM inserted



IF an UPDATE occurred, at least one column changed.  Since you're capturing the entire row when a column changes, I don't see why you need any checks at all, unless you're limiting it to just certain columns changing.

If so, you can use an IF UPDATE() with ORs for that:

IF UPDATE(col1) OR UPDATE(col2) OR ...
    INSERT INTO dbo.Segment_History
    SELECT * FROM inserted
0
 

Author Comment

by:PtboGiser
ID: 38744873
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

0
 

Author Comment

by:PtboGiser
ID: 38744882
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.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 334 total points
ID: 38744894
Yep.  But you still need the INSERT part of it of course :-) :


...
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)
    INSERT INTO dbo.Segment_History
    SELECT * FROM inserted
0
 

Author Comment

by:PtboGiser
ID: 38744936
Thanks for your help as always guys. Some of this is starting to make sense!
0
 

Author Comment

by:PtboGiser
ID: 38744961
Yes added the insert. a little early posting back
Thanks again Scott
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38745397
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.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38745420
>>
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38745998
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.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question