TRIGGER after UPDATE

[Wojto]

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

[Wojto]

P.S.
The script must be MS SQL Server 2005 compatibile if it cares :-)

[Erland Sommarskog]

Wojto (je***********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Wojto]

Thanx for Your reply! It was really helpfull for me and I really
appreciate it! ALTER_TABLE works good. But in the matter of fact now I
see that this solution is really far from beeing perfec...

Regards,
Wojtek