"how to insert" puzzle
-
I have three tables: invoice header, invoice detail, product. I want to be able to automatically generate a new invoice for each new product added to the product table.
How do I do that?
That is, when I run the following sql statement:
insert into product(product_name) values('red book');
I want to be able to automatically insert a record into the invoice header table and in the invoice detail table.
I know it sounds simple but not sure how to approach this.
Below is a snippet of the tables I am using.
/* --------------- */
use tempdb
create table product(
product_id int identity(1,1) primary key,
product_name varchar(10) not null);
create table invoiceheader(
header_id int identity(1,1) primary key,
header_date smalldatetime not null);
create table invoicedetail(
detail_id int identity(1,1) primary key,
header_id int not null constraint fk_detail_header_id references invoiceheader(header_id),
product_id int not null constraint fk_detail_product_id references product(product_id));
/* ---------------- */
Sincerely,
Billy
-
You can insert records into the desired tables in the INSERT trigger on the product table. It will garantee you that in case of any INSERT into product teo other inserts will run.
My preference however would be to create a stored procedure called pr_insert_product and put all your insert statements in the right order their. Then you would need to make sure any INSERT into product is executed only through that stored procedure.
If your question is how to learn the product_id (an identity) right after the INSERT, you can read it immediately after the INSERT statement like this:
SELECT @product_id = @@IDENTITY
-
If I create a trigger on the product table to insert a record into invoice header table, then how do I insert a record in the invoice detail table? the values generated by the identity field in the invoice header (which I need to insert into the invoice detail table) are not accessible from the trigger on the product table.
-
I am sure it is accessible by using @@IDENTITY. But again, put it all in one stored procedure and read @@IDENTITY right after an INSERT statement (into product table and then into invoice table).
-
thanks mromm.
Creating a proc (eg. usp_insert_product) using @@identity works if you only have one product (as in the example I raised).
However, what if you are inserting more than one product at a time?
For example, I have another table that accumulates product names. This accumulated table has two records: red book and blue book. Therefore I would execute the following statement:
INSERT INTO PRODUCTS SELECT PRODUCT_NAME FROM accProducts
In the above example, I want to be able to generated an invoice for each product. (ie. a total of two records inserted into invoice header and two records inserted into invoice detail).
I guess using the usp_insert_product will work via a cursor on the accProduct table. However, I wondering if there is a better way without loading the accProducts table into a cursor and looping through it.
Sincerely,
Billy
-
Triggers are definitely the way to go in my opinion.
If I'm not mistaken, the identity value is part of the inserted table, so that's no problem either.
To get the id value of the header table, you can again use a trigger.
So you will have a trigger on the product table, inserting into the header table and a trigger on the header table that inserts the corresponding record in the detail table. (Phew, words seem so awkward at times
)To handle multiple simultaneous inserts, you have to make sure your trigger supports it. Either use a cursor to loop through the inserted table (don't do it, if you can avoid a cursor !!!) or make sure you have a clean set based solution.
-
The INSERT...SELECT and getting the identities back out does not exist for SQL Server of any version. This is a problem that many people would love to see a solution to. Read the first sentence in the remarks here :http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_50u1.asp
There are several homegrown solutions out there, but none are very attractive. One that I use and like as the lessor of all evils is this :
1) Add a guid column to your table
2) create a guid variable in your stored proc / trigger code, assign it the value of NEWID(), and use it with the INSERT...SELECT, inserting it into the column added in step 1
3) Then you can select all of the id's back out of that table where the table guid equals your variable guid.
If anyone has a cleaner solution than that I would greatly love to hear it.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food -
Seems like nested triggers (on product and invoice header tables) will solve the problem if you use the inserted table inside of each trigger.
To avoid using trigger and do it all in SP, you could try this. I neverdid it but worth trying. select @@ROWCOUNT and @@IDENTITY right after the INSERT. if your identity seed is 1, then your ID range or the records just inserted is probably @@IDENTITY through @@IDENTITY+@@ROWCOUNT (or less likely @@IDENTITY-@@ROWCOUNT through @@IDENTITY).
I would like to know the results if this solution sound attractive to you.
-
Also, nested triggers have to be enabled for the entire database - a very dangerous thing if you had not carefully designed for this behavior from the start.
)Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply