Posted by Razvan Socol on 05/20/07 06:39

Hello, Shane

When you write triggers, you should not assume that the INSERTED table
will always contain only one row.

Let's consider the following DDL and sample data:

CREATE TABLE Countries (
CountryCode char(2) PRIMARY KEY, --ISO 3166-1 alpha 2
CountryName varchar(50) UNIQUE
)

INSERT INTO Countries VALUES ('US','United States')
INSERT INTO Countries VALUES ('GB','United Kindom')
INSERT INTO Countries VALUES ('FR','France')
INSERT INTO Countries VALUES ('RO','Romania')

CREATE TABLE teams (
TeamName varchar(50) PRIMARY KEY,
CountryCode char(2) --REFERENCES Countries
)

I would write the following trigger:

CREATE TRIGGER teams_IU_CheckCountry ON teams
FOR INSERT, UPDATE
AS
IF @@ROWCOUNT>0 AND UPDATE(CountryCode) BEGIN
IF EXISTS (
SELECT * FROM inserted
WHERE CountryCode NOT IN (SELECT CountryCode FROM Countries)
) BEGIN
RAISERROR ('Incorrect country code !',16,1)
ROLLBACK
RETURN
END
END

We can check how it works using these statements:

INSERT INTO teams VALUES ('Chicago Bulls', 'US')
INSERT INTO teams VALUES ('Steaua Bucuresti', 'RO')

INSERT INTO teams SELECT Team, 'GB' FROM (
SELECT 'Manchester United' Team
UNION ALL SELECT 'Arsenal'
) x

INSERT INTO teams VALUES ('Juventus', 'IT')
--the last INSERT will fail because we have not entered the country
code for Italy

However, this trigger is not enough to ensure referential integrity,
we also need a trigger for the Countries table:

CREATE TRIGGER countries_UD_CheckTeams ON Countries
FOR UPDATE, DELETE
AS
IF @@ROWCOUNT>0 BEGIN
IF EXISTS (
SELECT * FROM teams
WHERE CountryCode IN (SELECT CountryCode FROM deleted)
AND CountryCode NOT IN (SELECT CountryCode FROM Countries)
) BEGIN
RAISERROR('This country code is used by a team !',16,1)
ROLLBACK
RETURN
END
END

GO
DELETE Countries WHERE CountryCode='FR'
-- works OK

DELETE Countries WHERE CountryCode='RO'
-- error, because we have a team

Of course, instead of using such triggers, it's much more prefferable
to have real foreign keys, because of performance reasons (both for
modifications and SELECT-s), ease of development (why write 20 lines
of code when you can write 2 words?), functionality (we can create a
cascading FK), etc.

Razvan

[Back to original message]