SQL - Update values in a column upon insert

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
PtboGiserAsked:
Who is Participating?
 
Anthony PerkinsCommented:
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
 
Surendra NathTechnology LeadCommented:
when you are saying not working, can you please elaborate, i.e. can you tell us whats happening with the current code...
0
 
PtboGiserAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Surendra NathTechnology LeadCommented:
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
 
PtboGiserAuthor Commented:
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
 
Surendra NathTechnology LeadCommented:
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
 
awking00Commented:
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
 
PtboGiserAuthor Commented:
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
 
PtboGiserAuthor Commented:
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
 
Surendra NathTechnology LeadCommented:
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
 
PtboGiserAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
PtboGiserAuthor Commented:
Thanks Perk
Thas true, with Recursive Triggers Enabled set to false, What is the better way to go at this problem?
Suggestions?
0
 
Anthony PerkinsCommented:
Why are you using a TRIGGER for this?  Why not do it directly in the INSERT statement?
0
 
PtboGiserAuthor Commented:
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
 
Anthony PerkinsCommented:
So you think that doing two INSERTs instead of one is more efficient?
0
 
PtboGiserAuthor Commented:
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
 
PtboGiserAuthor Commented:
Thanks Perk, I'll check into this monday. as i'm off today.
0
 
Anthony PerkinsCommented:
Thanks Perk,
Most people here call me by my name: Anthony.
0
 
PtboGiserAuthor Commented:
Good to learn your name thanks. I working on this now, hopefully get it figured out.
0
 
PtboGiserAuthor Commented:
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
 
PtboGiserAuthor Commented:
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
 
Anthony PerkinsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.