Hi
You have very little scope to insert new records in a secondary table if you
use a trigger, as you can not pass parameters to it. If there is only one
column in the second table it should be redundant. If there are other
columns then you should write a stored procedure and use a transaction to
maintain consistency see books online :
BEGIN TRANSACTION:
mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_ba-bz_96zy.htm
ROLLBACK TRANSACTION:
mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_ra-rz_471q.htm
COMMIT TRANSACTION:
mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_ca-co_7w6m.htm
You should be able to identify the values inserted in a session by including
identifying data in the table such as Username, Datatime, SessionId etc.
John
"Scrappy" <ce*****@lan-specialist.com> wrote in message
news:bn******************@twister.nyroc.rr.com...
Thanks! I used a trigger to accomplish this. I am new to SQL. Are there
any pitfalls with doing it with a trigger?
Also....
On the same confirmation page I want to diplay links to a page for each
table. Basically I need to select the entry field from each table that I
have inserted to with the trigger. I can then use this as a hyperlink to
the each page. Any ideas on this?
"Bruce Loving" <BR***@LOVINGSCENTS.COM> wrote in message
news:tb********************************@4ax.com...SELECT scope_identity() AS entry does not assign the identity to a variable named entry, it just
returns a recordset like any other select
either
declare @Entry int
set @Entry = (select scope_identity())
insert into table (field) values (@Entry)
or
insert int table (field) values (select scope_identity())
also move the set nocount off to the top
On Fri, 20 Feb 2004 19:40:54 GMT, "Scrappy"
<ce*****@lan-specialist.com> wrote:
I have an ASP front end on SQL 2000 database. I have a form that
submits toan insert query. The entry field is an "identity" and the primary key. Ihave used scope_identity() to display the entry# of the record just enteredon the confirmation page. Now I need to insert the entry into another
table. This is my query:
SET NOCOUNT ON
INSERT wo_main
(site_id, customer, po_number)
VALUES ('::site_id::', '::customer::', '::po_number::')
SELECT scope_identity() AS entry
INSERT INTO wo_combo_body
(entry) VALUES ('::entry::')
SET nocount off
This query displays the entry number of the record just entered, but insertsa 0 in to entry field of the 2nd table. Any help would be great.
Thanks,
Darren