Need some help with a Trigger

I created an instead of insert trigger which checks to see if the "key" of
the inserted record already exists. If it does, it copies the existing
record to another table, deletes it and inserts the new one. The problem I
get is when the insert statement coming into the trigger looks like the one
below I don't get my idx returned.
insert into resdata(resdata, data, alf) values (@.p1, @.p2, @.p3) select
scope_identity() as idx
here's the trigger:
ALTER TRIGGER [dbo].[tg_audit] ON [dbo].[resdata] WITH EXECUTE AS CALLER
INSTEAD OF INSERT AS
declare @.count int, @.comp int
declare @.resft int, @.data float, @.alf datetime
select @.resft = inserted.resft, @.data = data, @.alf = inserted.alf from
inserted;
select @.comp = idx from research where idx in (select research from resft
where idx = @.resft)
select @.count = count(data)from resdata where resft = @.resft and data =
@.data
if (@.count > 0) /* This record already exists so we don't want it added*/
return; // Don't know what to put here
else
begin
/* Create the same record in the history table */
insert into resdatah (resft, alf, data, ohm) select resft, alf, data,
getdate() from resdata where resft = @.resft
/* Delete the existing record from the this (resdata) table */
delete from resdata where resft = @.resft
/* Insert the new record */
insert into resdata (resft, data, alf) select resft, data, alf from
inserted
end"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:%23NWrFOcGFHA.3648@.TK2MSFTNGP09.phx.gbl...
> ALTER TRIGGER [dbo].[tg_audit] ON [dbo].[resdata] WITH EXECUTE AS CALLER
Please move this into the SQL Server 2005 newsgroups...
http://www.aspfaq.com/sql2005/show.asp?id=1
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||I'm actually using 2000 and 2005 (for testing). The trigger needs to work in
2000.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#mcqNYcGFHA.1528@.TK2MSFTNGP09.phx.gbl...
> "Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
> news:%23NWrFOcGFHA.3648@.TK2MSFTNGP09.phx.gbl...
> Please move this into the SQL Server 2005 newsgroups...
> http://www.aspfaq.com/sql2005/show.asp?id=1
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:u2r3zbcGFHA.2748@.tk2msftngp13.phx.gbl...
> I'm actually using 2000 and 2005 (for testing). The trigger needs to work
in
> 2000.
The EXECUTE AS syntax is new for 2005 so you're going to have some
problems there...
Anyway, two suggestions: One, this might be a case where @.@.IDENTITY
should be used rather than SCOPE_IDENTITY() -- since the insert is being
done in the scope of the trigger, not in the scope of the initial INSERT
statement, @.@.IDENTITY should return the correct value. Second, you could
SELECT SCOPE_IDENTITY() within the trigger after you do the insert.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Your trigger will fail if more than one row is inserted. Never write
triggers like that.
When you have an INSTEAD OF trigger the @.@.IDENTITY returns the last
inserted IDENTITY value but SCOPE_IDENTITY() won't. Neither are very
useful within a trigger itself because a trigger should always be able
to handle multiple row inserts.
EXECUTE AS CALLER isn't supported in SQL2000.
Please post DDL and sample data INSERTs if you need more help.
David Portas
SQL Server MVP
--|||ok so how do I set the value of idx for the inserted return?
idx = @.idx // if record already existed
and
idx = select_scope_identity() if the trigger inserted the row?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#7Qr6ecGFHA.552@.TK2MSFTNGP12.phx.gbl...
> "Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
> news:u2r3zbcGFHA.2748@.tk2msftngp13.phx.gbl...
work
> in
> The EXECUTE AS syntax is new for 2005 so you're going to have some
> problems there...
> Anyway, two suggestions: One, this might be a case where @.@.IDENTITY
> should be used rather than SCOPE_IDENTITY() -- since the insert is being
> done in the scope of the trigger, not in the scope of the initial INSERT
> statement, @.@.IDENTITY should return the correct value. Second, you could
> SELECT SCOPE_IDENTITY() within the trigger after you do the insert.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||This table is a read-only table except for a single user. The inserts are
done like this:
insert into resdata(resdata, data, alf) values (@.p1, @.p2, @.p3) select
scope_identity() as idx
The goal is to check and see if there is a row with resdata =
inserted.resdata. If so, the existing record needs to be copied to another
table and the new one inserted.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109177795.995989.220250@.f14g2000cwb.googlegroups.com...
> Your trigger will fail if more than one row is inserted. Never write
> triggers like that.
> When you have an INSTEAD OF trigger the @.@.IDENTITY returns the last
> inserted IDENTITY value but SCOPE_IDENTITY() won't. Neither are very
> useful within a trigger itself because a trigger should always be able
> to handle multiple row inserts.
> EXECUTE AS CALLER isn't supported in SQL2000.
> Please post DDL and sample data INSERTs if you need more help.
> --
> David Portas
> SQL Server MVP
> --
>|||I'm not sure why @.@.IDENTITY doesn't meet your requirements as Adam
suggested but anyway IDENTITY should never be the only key of a table
therefore you can use an alternate key to retrieve the inserted
IDENTITY:
INSERT INTO x (key_col, ...) VALUES (@.key_col, ...)
SET @.id =
(SELECT id_col
FROM x
WHERE key_col = @.key_col)
David Portas
SQL Server MVP
--|||I'm . Given this insert statement (which is being generated from a
SqlDataAdapter):
insert into resdata(resdata, data, alf) values (@.p1, @.p2, @.p3) select
scope_identity() as idx
how do I reference column idx to set the value in both cases? 1 - when I
want to set it to an existing idx; 2- when I want to set it to the new idx?
In case 2 I can use the @.@.IDENTITY to get the identity of the column but how
do I assign it so it's returned back. Maybe I'm with the way the
SqlDataAdapter gets the values back.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109179471.440296.167900@.f14g2000cwb.googlegroups.com...
> I'm not sure why @.@.IDENTITY doesn't meet your requirements as Adam
> suggested but anyway IDENTITY should never be the only key of a table
> therefore you can use an alternate key to retrieve the inserted
> IDENTITY:
> INSERT INTO x (key_col, ...) VALUES (@.key_col, ...)
> SET @.id =
> (SELECT id_col
> FROM x
> WHERE key_col = @.key_col)
> --
> David Portas
> SQL Server MVP
> --
>|||"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:u9rGUwcGFHA.332@.TK2MSFTNGP10.phx.gbl...
> This table is a read-only table except for a single user. The inserts are
> done like this:
> insert into resdata(resdata, data, alf) values (@.p1, @.p2, @.p3) select
> scope_identity() as idx
I could be wrong, but it looks to me like that is two separate statements:
1) insert into resdata(resdata, data, alf) values(@.p1, @.p2, @.p3)
2) select scope_identity() as idx

> The goal is to check and see if there is a row with resdata =
> inserted.resdata. If so, the existing record needs to be copied to another
> table and the new one inserted.
I think what your trigger needs to do is something like:
--Backup Matched Rows
INSERT INTO backuptable
SELECT * FROM maintable INNER JOIN inserted ON
maintable.resdata=inserted.resdata
--Delete Matched Rows
DELETE From maintable
WHERE EXISTS(
SELECT * from mainTable INNER JOIN inserted
ON maintable.resdata = inserted.resdata)
--Add All Rows
INSERT Into maintable
SELECT * from Inserted
Good Luck,
Jim