|
|
Posted by Erland Sommarskog on 05/20/07 22:36
Shane (shane@weasel.is-a-geek.net) writes:
> 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.
If there is a key in this table, then it is not that tricky:
CREATE TRIGGER no_more_teams ON tbl AFTER INSERT, UPDATE AS
IF EXISTS (SELECT *
FROM inserted i
WHERE NOT EXISTS (SELECT *
FROM tbl
WHERE tbl.teamid <> i.teamid
AND tbl.country = i.country))
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('No more countries permitted!', 16, 1)
END
> create trigger chk_team
> on teams
> for insert as
> declare @chkCountry as char(2)
> select @chkCountry = (select country from INSERTED)
Again: you must not do this. A trigger must be able to handle the
situation more than one row is inserted, so you cannot select into
a variable.
....Wait! Slap me on the face! The trigger above will also not handle
multi-row inserts correctly, but may permit new countries in this case.
OK, so it is a bit more tricker. What about:
IF EXISTS (SELECT *
FROM (SELECT country, COUNT(*) AS cnt
FROM inserted
GROUP BY country) AS i
JOIN (SELECT country, COUNT(*) AS cnt
FROM tbl
GROUP BY country) AS t ON t.country = i.country
WHERE t.cnt = i.cnt)
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('No more countries permitted!', 16, 1)
END
Or write an INSTEAD OF trigger, in which case your original logic
will work. But you still need to handle multi-row inserts.)
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|