June 24, 2010 at 12:45 pm
hello,
I have 2 table tbl1,tbl2 in the relation 1:N.
If I want insert records into tbl2 i need to know FK of tb. I found function identity scope that returns last id.
So my questions if how may I store value???
One way is create trigger like this:
CREATE TRIGGER test
AFTER INSERT
AS BEGIN
-- Main block of the trigger here
SET @sql='select identity_scope as last_id into #tmp'
EXECUTE(@SQL)
END
and use temporary table, but i dont know if its good way,because there is many people which is connected to the DB.
THX a LOT. Radek
June 24, 2010 at 12:59 pm
How are you inserting the values? Can you show some code? Typically you don't set values like you did in the trigger as it means you can't handle multiple inserts at once.
June 24, 2010 at 1:18 pm
In this monemt I have not code its in the paper:)
Values will inserting manually like this INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
Thin client is created in MS ACCESS 2003 🙁
in T1 is id, data,user_ID,id_line
in T2 is table with failures on the production line.
operators create audit ones the week on the production line,
If they didnt find any mistakes they click on the button and there is code like insert into T1..
record will be only in the table T1 .. it is will mean that on the line isnt any mistakes.
If operators find any mistakes on the production line they will click on the button
in T1 will be date.. and now I need insert FK into T2 and one record will one mistakes...
Thx Radek
June 24, 2010 at 1:21 pm
Typically the client code is in a batch so
declare @id int
insert Table1 select @myval
set @id = scope_identity()
insert table2 select @id, @mynewval
You don't need to Exec() the SQL is you store this. However depending on how this works in Access, you may or may not be able to use something like this:
CREATE TRIGGER test
AFTER INSERT
AS BEGIN
-- Main block of the trigger here
select identity_scope as last_id into #tmp
END
June 24, 2010 at 1:45 pm
ok,thanks.
And if I will use temp table is there values only from current sessions or from all sessions?
Thx Radek
June 24, 2010 at 2:24 pm
Temp tables with one # are visible to your session only.
Those with ## (Create table ##Temp) are visible to all sessions
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply