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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-05-23 : 00:34:55
|
Jen writes "Okay, here we go...
I have 3 tables: TableA, TableB, and TableC. Table C relates back to its parent table B and TableB relates back to its parent table A. I am trying to create a trigger on table B that will check to make sure the clustered keys on a its parent table, table A exist before it allows for an insert. I have that part done and working. Heres where it gets tricky... table C relates to its parent table (back to the table B) that also needs this trigger to fire before an insert can be made on table C. Well I have tried copying and pasting the triggers from table b to table C and changing the names to be specific to table C. Its giving me all kinds of errors! When I then try to insert and update on table B, the trigger that is being fired is actually not even on this table, it fires the trigger for table C!
Here is the trigger I have so far for table B
CREATE trigger [PrimaryKey1onTableB] on [TableB]
FOR INSERT, UPDATE
AS
IF UPDATE (PrimaryKey1)
BEGIN
IF (EXISTS (SELECT inserted.PrimaryKey1 FROM inserted, TableA
WHERE TableA.PrimaryKey1 = inserted.PrimaryKey1 AND TableA.PrimaryKey2 = inserted.PrimaryKey2 AND TableA.PrimaryKey3 = inserted.PrimaryKey3))
BEGIN
Update TableB
SET TableB.PrimaryKey1 = inserted.PrimaryKey1
FROM TableB, inserted
WHERE TableB.PrimaryKey1 = inserted.PrimaryKey1 AND TableB.PrimaryKey2 = inserted.PrimaryKey2 AND TableB.PrimaryKey3 = inserted.PrimaryKey3 AND TableB.PrimaryKey4 = inserted.PrimaryKey4
End
ELSE
BEGIN
RaisError('Record Does Not Exist in Primary Reference Table', 16, 1)
Rollback
END
END
This trigger works fine as long as Table C doesnt have any of its Primary Key Check triggers. What can I do to make this work correctly????
Thank you!!!
Jen" |
|
|
|
|
|
Creating Triggers that check for Existing Primary keys in Parent Tables