April 4, 2002 at 11:56 pm
Has anyone heard of an easy way to convert triggers to foreign keys?
We have an "old" database of 200+ tables which was created from upsizing an access database to SQL 6.5. The upsize wizard created triggers to enforce FK-rules in 6.5.
I would like to find a tool to easily converting our relations from triggers to FK's.
April 5, 2002 at 5:08 am
I have not come across any tool and never had an issue with our upgrades (we were not using FKs thou). Can you post an example trigger built like this and I may be able to find a solution.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 7, 2002 at 7:24 am
We have a table called Contract. It’s connected to a bunch of other tables.
Below I’ve included three sample triggers – one for delete, one for insert and one for update.
CREATE TRIGGER Contract_DTrig ON dbo.Contract FOR DELETE AS
/*
* CASCADE DELETES TO 'ContractLine'
*/
DELETE ContractLine
FROM deleted, ContractLine
WHERE deleted.LM_Portfolio = ContractLine.LS_Portfolio
AND deleted.LM_Contract = ContractLine.LS_Contract
CREATE TRIGGER Contract_ITrig ON dbo.Contract FOR INSERT AS
/*
* PREVENT INSERTS IF NO MATCHING KEY IN 'Customer'
*/
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM Customer, inserted
WHERE (Customer.CF_Portfolio = inserted.LM_Portfolio
AND Customer.CF_Customer = inserted.LM_Customer))
BEGIN
RAISERROR(778744, 16, 1)
ROLLBACK TRANSACTION
GOTO ENDTRIGGER
END
CREATE TRIGGER Contract_UTrig ON dbo.Contract FOR UPDATE AS
/*
* PREVENT UPDATES IF NO MATCHING KEY IN 'Customer'
*/
IF UPDATE(LM_Portfolio) OR UPDATE(LM_Customer)
BEGIN
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM Customer, inserted
WHERE (Customer.CF_Portfolio = inserted.LM_Portfolio
AND Customer.CF_Customer = inserted.LM_Customer))
BEGIN
RAISERROR(778743, 16, 1)
ROLLBACK TRANSACTION
END
END
/*
* CASCADE UPDATES TO 'ContractLine'
*/
IF UPDATE(LM_Portfolio) OR UPDATE(LM_Contract)
BEGIN
UPDATE ContractLine
SET ContractLine.LS_Portfolio = inserted.LM_Portfolio ,
ContractLine.LS_Contract = inserted.LM_Contract
FROM ContractLine, deleted, inserted
WHERE deleted.LM_Portfolio = ContractLine.LS_Portfolio
AND deleted.LM_Contract = ContractLine.LS_Contract
END
April 7, 2002 at 7:47 am
I have not seen a tool to do this either. SQL2K supports cascade update/delete in addition to foreignkeys. Easiest way I've seen to implement them is via the diagramming tool.
Andy
April 7, 2002 at 7:51 am
Hoped to avoid this... it basically involves scripting everything and starting over with our 200+ tables.
Thanks anyways!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply