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

Erland Sommarskog wrote:

> DA Morgan (damorgan@psoug.org) writes:
>>> The trigger fires *after* an insert has taken place, therefore in the
>>> second case the trigger rollsback because the country does now indeed
>>> exist in the subquery
>>>
>>> Any ideas?
>>
>> That is one of the major problems with a product that only has AFTER
>> triggers. In those products that have both BEFORE and AFTER triggers,
>> AFTER triggers are used for auditing and BEFORE triggers used for
>> security and integrity. Essentially you are trying to do the right
>> thing, in the wrong way, in a product that truly doesn't support it.
>
> Actually, SQL Server also has INSTEAD OF triggers, which is not really
> the same thing as an BEFORE trigger. Since an INSTEAD OF trigger requires
> you to redo the action that trigger it, it less apetizing for checks -
> unless the check is of the kind "DELETE is not permitted on this table".
>
> But a BEFORE trigger would not have help Shane, as his trigger seemed to
> perform a check against existing data in the table. The logic appears
> to be "it's OK to insert UK in the table if it's already there". His
> AFTER trigger permits everything. But a BEFORE trigger would have kept
> the table empty.
>
>
>
>

Hi
Yes you are correct, my trigger would keep a new table empty, however this
trigger is being written for an existing table, that has existing entries.
I think the point of my trigger is supposed to keep the country list static.

I have used the following trigger, however I am not happy with it, as the
values are hard-coded.

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 ('NZ', 'AU', 'SA')

BEGIN
raiserror('Impossible country entered', 16, 1)
ROLLBACK TRANSACTION
END

--
Q: How can you tell that a mathematician is extroverted?
A: When talking to you, he looks at your shoes instead of at his.

[Back to original message]