trigger with select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

trigger with select

Author

Topic

snaso
Starting Member

21 Posts

Posted - 2004-09-01 : 03:51:07

Hi All,
I'm using a trigger for insert in my SQL server 200 DB:


CREATE TRIGGER PAT_INSERT 
ON PATIENT 
FOR INSERT AS 
BEGIN  

SELECT PATIENTCODE FROM INSERTED 

UPDATE PATIENT  SET BARCODE=PATIENT.BARCODE 
FROM INSERTED 
WHERE PATIENT.PATIENTCODE = INSERTED.PATIENTCODE  

END

As you see there's a select from inserted. This is used in an ASP page in order to get the primary key just inserted and it works fine.
The question is:
is there a way to catch the result from this trigger (i.e. the selected key) in the query analyzer? I hope my question will be more clear with this "pseudo"-code:


declare @return int
select @return = insert into patient(patientcode, surname, name, dt, pgm, id, risusercode)
values(getkey(), 'testinsert', 'testinsert', getdate(), 'test', 1, '0000000000')

I am aware this is obscene, but is only for explain my aim.
I hope someone has good idea, bye bye.

-snaso.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-01 : 06:16:34

well you could use @@identity to get a PK back if its an Indentity field

Go with the flow & have fun! Else fight the flow :)

snaso
Starting Member

21 Posts

Posted - 2004-09-01 : 06:22:30

ok, here's my weakness: the PK is not an identity :(
I hope there's a method, anyway....
I continue listening, Regards,

-snaso.

snaso
Starting Member

21 Posts

Posted - 2004-09-01 : 06:29:37

Also because when you write the "insert" clause in Query Analyser, you can see the selected field from trigger in the Grids window of the GUI.
So I suppose it is possible to catch this output in a TSQL variable... am I wrong?

-snaso.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-01 : 07:48:10

since u use getdate() in your insert. the last inserted key will be:

select top 1 patientcode
from patient
order by dt desc

Go with the flow & have fun! Else fight the flow :)

snaso
Starting Member

21 Posts

Posted - 2004-09-01 : 07:58:50

Thank you spirit1, but this is not what I want.
The problem with you're method is the concurrency: if two clients run the insert statement in the same moment it became possible that the select gives the wrong result (i.e. the last inserted in patient), while if I was able to catch the result given by the trigger I am sure it's the correct one....
Thanks again, bye,

-snaso.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources