|
|
Posted by Shane on 05/20/07 02:55
I have been instructed to write a trigger that effectively acts as a foreign
key. The point (I think) is to get me used to writing triggers that dont
use the primary key(s)
I have created the following trigger
create trigger chk_team
on teams
for insert as
declare @chkCountry as char(2)
select @chkCountry = (select country from INSERTED)
-- if @@ROWCOUNT =0 RETURN
If @chkCountry NOT IN
(select distinct country from teams)
BEGIN
raiserror('Impossible country entered', 16, 1)
ROLLBACK TRANSACTION
END
However I tested it with the following insert statement
insert into teams values (15, 'London Paris', 'UK', 'Clive Woodward', 0,
NULL)
Which (unfortunately) works, IOW the above insert statement should cause the
error I specified as 'UK' does not exist in the set "select distinct
country from teams"
Any help appreciated
Navigation:
[Reply to this message]
|