|
|
Posted by Erland Sommarskog on 05/14/07 21:34
Wojto (jestem.wojtek@interia.pl) writes:
> I need to write a trigger that will check referential integrity of my
> data. I have few FOREIGN KEY constraints but, as You probably konow, the
> cannot be deferred (in the meaning of SQL 92 standard). So I decided to
> add NOCHECK CONSTRAINT ALL to the modified table and then run a trigger
> (after secon altertion of my table). But I cannot write a trigger for
> ALTER. I found something on msdn, byt their example doesn't work. To
> show what's my problem look at this example:
I'm afraid that the idea does not seem workable to me. At least it would
be a serious kludge. While I can agree that commit-time constraints had
been nice, I would recommend you to find a solution within the rules.
> CREATE TRIGGER ReferentialIntegrityTriggerForStudenci
> ON DATABASE
> AFTER ALTER
You would need AFTER ALTER_TABLE.
> AS
> BEGIN
> DELETE FROM Studenci WHERE Studenci.Indeks NOT IN (SELECT id_indeksu
> FROM Indeksy);
> END
But you don't want this code to be run each time someone performs
ALTER TABLE, so you would have read examine the result of the
eventdata() function to see if the statement apply to your tables.
As I said, it would be an enormous kludge, I definitely recommend you
to look for a different solution for your actual problem.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|