Solved

SQL - Update values in a column upon insert

Posted on 2013-05-23
23
226 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
  • 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
 

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 31

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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 400 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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 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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now