Another Trigger - 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

General SQL Server Forums

New to SQL Server Programming

Another Trigger

Author

Topic

cm2adams
Starting Member

6 Posts

Posted - 2009-07-13 : 00:08:37

Hi,

I have another problem that I need help with. I have two tables. One with the primary key and one with the foreign key. However I create the foreign key rows initially with a NULL reference to the primary key table.

When I create a new row in the Primary key table, I want to link some of the NULL referenced rows from the foreign key table based on the value of another column in the foreign key table.

The two tables are

Table1_MRI
Columns
pk_mriID
etc...

Table2_Claims
Columns
fk_mriID = NULL
Status ='Ready'
etc...

When I create a new row in Table1_MRI, I want the trigger to search Table2_Claims and make fk_mriID = pk_mriID AND Status = 'Waiting' for each row in Table2_Claims where the Status = 'Ready'

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-13 : 00:36:02

What if there are more than 1 records being inserted into Table1_MRI ? Which record to use ?

The following does not handle the situation i described above.


update c
set    fk_mriID = i.pk_mriID,
       Status   = 'Waiting'
from   INSERTED i
       INNER JOIN Table_2_Claims c ON  c.fk_mriID IS NULL
                                   AND c.Status = 'Ready'

KH
[spoiler]Time is always against us[/spoiler]

cm2adams
Starting Member

6 Posts

Posted - 2009-07-13 : 00:38:07

Only 1 record will be inserted into Table1_MRI at a time.

That works perfectly!

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources