Insert Triggers

[kafka]

So I first tried to solve the problem below using Stored Procedures, but then I thought, why not try Triggers.

Table 1 has a field called id1. It is the primary key and is an auto-generated incrementing in an ascending order by 1.

Table 2 has a primary key called id2. The primary
key is an auto-generated number incrementing in an ascendingorder by 1. It has a foreign key called id1, which is the primary key of Table 1.

So this is how the scenario works out. A HTML form submits to the database. The application logic inserts a record into Table 1 and Table 2 at the same time. As it writes to Table 2 I need the Stored Procedure to grab the last primary key id from Table 1 and insert it as the candidate key.

Create TriggertrgInsertNewId

On Table1

For Insert AS



Set nocount on

Insert Table2 (id1)

Select (id1)

FROM Insert

Go



This trigger pushes the latest id1 value from Table1 to the Foreign key id1 in Table2. There is a slight problem though.



When my form submits to the database I am doing two inserts, one to Table1 and the other to Table2. So the First insert adds data to Table1. The Trigger is kicked off.



So the problem is that when I do an insert for Table 2 a new row is created. Instead of the values being inserted in that same row where we just added the value for the Foreign key by way of the trigger a new row is created a I get a null value for the id1 foreign key field for Table2. Is there is a way for me to add the values of the rest of the fields in Table2 in the trigger statement? If so how do you do it?

Cheers,

G

 

[JohnDTampaBay]

The problem is that you need id1 from the table1 INSERT prior to issuing the INSERT into table2. I think I would solve this issue with a Stored Procedure, as you suggested. Write a proc similar to this:

CREATE PROC usp_DoTwoInserts
  @Name varchar(30) NOT NULL,
  @Address varchar(30) NOT NULL,
  @City varchar(30) NOT NULL,
  @State char(2) NOT NULL
AS
BEGIN
  DECLARE @id int

  --Do first INSERT and capture the ID generated
  INSERT Table1 (Name) VALUES (@Name)

  SET @id = @@IDENTITY

  --Use ID from first INSERT for second INSERT
  INSERT Table2 (id1,Address, City, State)
  VALUES (@id, @Address, @City, @State)
END

You may want to add some error checking to make sure the INSERT into table1 was successful prior to INSERTing into table2. If you need to know the id value(s) generated by the INSERTs, you will have to add output variable(s). Does this come close to answering your question?
--Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
0 row(s) selected