?
Solved

SQL - Update values in a column upon insert

Posted on 2013-05-23
23
Medium Priority
?
249 Views
Last Modified: 2013-06-16
USE [CNTY_GIS]
GO
/****** Object:  Trigger [dbo].[FName_AName_Num_Lastdate_Cont_Segment]    Script Date: 05/23/2013 13:36:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Alter TRIGGER [dbo].[Add_500000_toID] ON [dbo].[City_Segment]
    AFTER INSERT
AS
    BEGIN
SET NOCOUNT ON;
	Update City_Segment		
        SET     ENTITY_ID = (s.ENTITY_ID + 500000)
              FROM    dbo.City_Segment s
                INNER JOIN INSERTED i ON s.ENTITY_ID = i.ENTITY_ID
              
    END

Open in new window


How does this look? i am looking to set Entity_ID to add 500000 to each record upon insert into the table.
Shold this work? Since i'm asking its not.  Whats wrong with it?
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
  • 12
  • 6
  • 4
  • +1
23 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39191917
when you are saying not working, can you please elaborate, i.e. can you tell us whats happening with the current code...
0
 

Author Comment

by:PtboGiser
ID: 39191935
When i execute in SQL MS it completes successfully, But when i paste new records into the table i do not get any results the value of 500,000 is not added to the Value in Entity Id
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39191956
ok, can you do this
Can you give the create table script for the City_Segment table
and then the insert script that you are trying to do on this.

and also can you try to execute the below statement let me know the results of the same

select * from sys.triggers where name = 'Add_500000_toID'

Open in new window

0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:PtboGiser
ID: 39191988
The insert was and create table where done with  a  GIS Software package.
Results are on the image attached.

the Trigger seems to work once i complete a new insert to the table, So if i draw or insert another line in the drawing the trigger fires and adds 500000 to each record at that point but not the original insert(copy and paste funciton is GIs Sortware).
Capture.PNG
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39192003
I dont know about the GIS software,
but a trigger will fire only after it is created.

consider a scenario like this
step 1: a table is created
step 2: 10 rows are inserted
step 3: a trigger is created
step 4: 5 rows are inserted

Now, the trigger will update the 5 rows created in step 4, but it will do nothing with rows created in step 2.

How to overcome this, create a trigger in step 2 and then insert values into the table as part of step 3 ( flip step 2 and step 3 ).
0
 
LVL 32

Expert Comment

by:awking00
ID: 39192099
I'm also not familiar with GIS software but, when I see you use the term "when I paste new records in the table ...", it seems likely that it no longer is an insert operation so the trigger never fires. Perhaps you can explain the pasting concept.
0
 

Author Comment

by:PtboGiser
ID: 39192235
Just like copy and Paste in access or excel, When i paste in a drawing it inserts the geometry and records into the table. Let me do somemore testing and get back to you.
0
 

Author Comment

by:PtboGiser
ID: 39192292
When i write the query in the Syntax i did, I'm thinking there is not match match between the
s.ENTITY_ID = i.ENTITY_ID, Because i added  ENTITY_ID = (s.ENTITY_ID + 500000) to the s.ENTITY_ID then when i try and do the join i get no results because the Id's Don't Match.
Does that make sense?
How do i fix this?
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39192308
No, that cannot be... your query is good...

Can I ask you to do one thing...

drop the existing table from sql server
Create your table from your GIS software,
then create the trigger in the sql server (exact trigger)
and then try to insert the values from gis software

now, check the results
0
 

Author Comment

by:PtboGiser
ID: 39204779
Working with Existing table/.I deleted all the records from my exisiting table so its blank.
Trigger is enabled.
I draw a new road in the drawing which automattically creates a record in the DB table. It has an Entity_Id of 60 which i populate before the record is commited to the DB Table.
I now look at the feild and its still 60. I refresh the drawing in my GIS sfotware and its still 60 so its not adding the value to my Enitity_ID.

Creating new table
Creating a new trigger
Same result - Value is not being updated when inserting a new line to the drawing.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39206446
That would be because you have Recursive Triggers Enabled set to false.

Mind you, I am not suggesting you set it to True to solve this problem, merely pointing out what is going on.
0
 

Author Comment

by:PtboGiser
ID: 39210701
Thanks Perk
Thas true, with Recursive Triggers Enabled set to false, What is the better way to go at this problem?
Suggestions?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39212242
Why are you using a TRIGGER for this?  Why not do it directly in the INSERT statement?
0
 

Author Comment

by:PtboGiser
ID: 39216022
Diredtly with an insert statement i have to run the query when changes are made right. So a trigger does it automatically so i dont have too.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39216078
So you think that doing two INSERTs instead of one is more efficient?
0
 

Author Comment

by:PtboGiser
ID: 39226651
I dont use and SQL insert statement, I use GIS software to update the records in the table. So the Software does the insert i just hit "paste"!
In the background its doing an insert yes, but i dont write that statement so upon the record being committted to my table is when i want the trigger to fire and update the Column with 500000
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1600 total points
ID: 39227609
Then your only option is to do something like this:
ALTER DATABASE YourDatabaseName  SET RECURSIVE_TRIGGERS ON

And then in your TRIGGER add code like this at the top or you will hit the maximum level of recursion pretty soon.

IF ((SELECT TRIGGER_NESTLEVEL()) > 1)
      RETURN

If there are any other TRIGGERs in the same database you will have to add code like this:
IF ((SELECT TRIGGER_NESTLEVEL()) > 0)
      RETURN
0
 

Author Comment

by:PtboGiser
ID: 39228802
Thanks Perk, I'll check into this monday. as i'm off today.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39230802
Thanks Perk,
Most people here call me by my name: Anthony.
0
 

Author Comment

by:PtboGiser
ID: 39245470
Good to learn your name thanks. I working on this now, hopefully get it figured out.
0
 

Author Comment

by:PtboGiser
ID: 39248639
Hey Anthony

Then your only option is to do something like this:
ALTER DATABASE YourDatabaseName  SET RECURSIVE_TRIGGERS ON

And then in your TRIGGER add code like this at the top or you will hit the maximum level of recursion pretty soon.

IF ((SELECT TRIGGER_NESTLEVEL()) > 1)
      RETURN

If there are any other TRIGGERs in the same database you will have to add code like this:
IF ((SELECT TRIGGER_NESTLEVEL()) > 0)
      RETURN

My code looks like so
USE [CNTY_GIS]
GO
/****** Object:  Trigger [dbo].[FName_AName_Num_Lastdate_Cont_Segment]    Script Date: 05/23/2013 13:36:55 ******/
IF ((SELECT TRIGGER_NESTLEVEL()) > 1)
      RETURN
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Alter TRIGGER [dbo].[Add_500000_toID] ON [dbo].[City_Segment]
    AFTER INSERT
AS
    BEGIN
SET NOCOUNT ON;
	Update City_Segment		
        SET     ENTITY_ID = (s.ENTITY_ID + 500000)
              FROM    dbo.City_Segment s
                INNER JOIN INSERTED i ON s.ENTITY_ID = i.ENTITY_ID
              
    END

Open in new window

It will run succefully but when i open the trigger a second time in design view that new line of code is gone. Not in th trigger? Why would that be
Thanks
0
 

Author Comment

by:PtboGiser
ID: 39248642
Also if i have 4 triggers on a table would i have to do ? as follows? Sorry still a novice level at this stuff.
IF ((SELECT TRIGGER_NESTLEVEL()) > 4)
      RETURN
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39251855
This is what your TRIGGER code should look like:
ALTER TRIGGER [dbo].[Add_500000_toID] ON [dbo].[City_Segment]
    AFTER INSERT
AS
    BEGIN
        SET NOCOUNT ON;

        IF ((SELECT TRIGGER_NESTLEVEL()) > 1)
			RETURN

        UPDATE  City_Segment
        SET     ENTITY_ID = (s.ENTITY_ID + 500000)
        FROM    dbo.City_Segment s
                INNER JOIN INSERTED i ON s.ENTITY_ID = i.ENTITY_ID
              
    END

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

719 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