|
|
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
Navigation:
[Reply to this message]
|