trigger that creates record and gets identity

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

trigger that creates record and gets identity

Author

Topic

timmyelliot
Starting Member

2 Posts

Posted - 2010-04-20 : 23:55:57

I have an mssql question:

I have two tables Table1 and Table2.

Table2 has a Foreign Key pointing back to Table1.

I have users inserting records to Table2, if a user doesn't set a value for the Foreign Key, I'd like a Trigger to
1. create a new record in Table1,
2. get the Identity for that new record, then
3. set the value of the Foreign key in Table 2 with the new Identity.

I don't know if this would help, but here's my pseudo-code. Any ideas are appreciated.

IF TABLE2.ForeignKeyField IS NULL
INSERT INTO TABLE1 (SomeColumn) VALUES(SomeValue)
TABLE2.ForeignKeyField = Scope_Identity()
END

(Sorry if the code ends up being confusing, I'm just learning mssql.)

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-21 : 00:12:27

Just to start with :

Assumption: Records will be inserted one by one and not bulkloaded.

Declare @pkval_1 int
Declare @FKval_1 int
Declare @FKval_2 int
select @pkval_1 = PrimaryKey column name, @Fkval_1 = foreignkey column name from inserted

if @FKval_1 is null
Begin

INSERT INTO TABLE1 (SomeColumn) VALUES(SomeValue)

Set @FKval_2 = Scope_Identity()

Update Table2 Set ForeignKeyField = @FKval_2 where Primarykey of table2 column = @pkval_1

end

Regards,
Bohra

timmyelliot
Starting Member

2 Posts

Posted - 2010-04-21 : 00:15:42

Thank you. That definately gets me on my way.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 01:22:16

is there a chance of batch insert into both the tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-21 : 02:19:45

You are welcome..

Remember the assumption i mentioned on top.

In case you are using batch insert then the approach needs to be different.

Kristen
Test

22859 Posts

Posted - 2010-04-21 : 02:24:40

At the very least the trigger has to check for that, and raiserror() if it occurs - otherwise heading for a fall.

But surely the whole point is that the FK is there to stop people adding Child records where there is no Parent, rather than creating a parent just-in-time? Strikes me that there is something wrong with the flow of work if this scenario will occur ... but an explanation of the situation would help I expect - we might have other ideas too

This will need to be an INSTEAD OF trigger, rather than an AFTER trigger, won't it? (The few I have had to write have been a lot more bother than AFTER triggers; part from having to remember to adjust the trigger if any new columns are added to the table I seem to remember we had issues with ARITHABORT and some other similar settings.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 05:40:28

If you're using SQL 2005, I think you can use OUTPUT operator for this rather than wrapping this inside a trigger

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources