SQL - Update values in a column upon insert

when you are saying not working, can you please elaborate, i.e. can you tell us whats happening with the current code...

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

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'

Select allOpen in new window

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

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 ).

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.

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.

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?

No, that cannot be... your query is good...

Can I ask you to do one thing...


now, check the results

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.

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.

Thanks Perk
Thas true, with Recursive Triggers Enabled set to false, What is the better way to go at this problem?
Suggestions?

Why are you using a TRIGGER for this?  Why not do it directly in the INSERT statement?

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.

So you think that doing two INSERTs instead of one is more efficient?

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

Thanks Perk, I'll check into this monday. as i'm off today.

Thanks Perk,
Most people here call me by my name: Anthony.

Good to learn your name thanks. I working on this now, hopefully get it figured out.

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

Select allOpen 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

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

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

Select allOpen in new window