|
|
Posted by Wojto on 05/14/07 16:47
Hi there!
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:
CREATE TABLE Indeksy(
id_indeksu INT CONSTRAINT indeksy_pkey PRIMARY KEY,
numer INT CONSTRAINT wymagany NOT NULL
CONSTRAINT unikatowy UNIQUE
);
CREATE TABLE Studenci(
id_studenta INT CONSTRAINT studenci_pkey PRIMARY KEY,
indeks INT CONSTRAINT indeks_studenta REFERENCES Indeksy(id_indeksu)
ON DELETE CASCADE
ON UPDATE CASCADE
--DEFERRABLE INITIALLY DEFERRED
CONSTRAINT tylko_raz UNIQUE,
nazwisko VARCHAR(255) CONSTRAINT nazwisko_wymagane NOT NULL
);
GO
CREATE TRIGGER ReferentialIntegrityTriggerForStudenci
ON DATABASE
AFTER ALTER
AS
BEGIN
DELETE FROM Studenci WHERE Studenci.Indeks NOT IN (SELECT id_indeksu
FROM Indeksy);
END
GO
ENABLE TRIGGER ReferentialIntegrityTriggerForStudenci ON Studenci;
GO
INSERT INTO Indeksy VALUES (1,1111111);
INSERT INTO Indeksy VALUES (2,1211111);
INSERT INTO Studenci VALUES (1,1, 'Kowalski');
INSERT INTO Studenci VALUES (2,2, 'Nowak');
--deferred
ALTER TABLE Studenci
NOCHECK CONSTRAINT ALL
INSERT INTO Studenci VALUES (3,5, 'Odraczacz');
INSERT INTO Studenci VALUES (4,130, 'Powolny');
INSERT INTO Studenci VALUES (5,4, 'Grabowski');
INSERT INTO Indeksy VALUES (3,1121111);
INSERT INTO Indeksy VALUES (4,1112111);
INSERT INTO Indeksy VALUES (5,1111211);
ALTER TABLE Studenci
CHECK CONSTRAINT ALL
When I run this script I get a message: Msg 156, Level 15, State 1,
Procedure ReferentialIntegrityTriggerForStudenci, Line 4
Incorrect syntax near the keyword 'ALTER'.
Without it INSERT INTO Studenci VALUES (4,130, 'Powolny'); inserts
invalid data that cannot be checked... Of course this is only an example.
Could you, please, write simillar, WORKING :-) trigger for me?
Thanx!
Wojciech
[Back to original message]
|