Posted by Shane on 05/20/07 03:16

Shane wrote:

> 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

I have got the triger working as I desire, however now I am perplexed as to
*why* it works..
The (new) trigger reads:

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 IN
        (select distinct country from teams)
BEGIN
        raiserror('Impossible country entered', 16, 1)
        ROLLBACK TRANSACTION
END


I am now _seriously_ confused
--
Q: Who knows everything there is to be known about vector analysis?
A: The Oracle of del phi!

[Back to original message]