Re: Trigger

Shane (shane@xxxxxxxxxxxxxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: Update Identity Column
    ... Actually, I implemented the trigger, and it seems to be working fine. ... Sylvain Lafontaine, ing. ... SQL Server will almost invariably decide that that's the best order to ... INSERT INTO tbl VALUES; ...
    (microsoft.public.access.adp.sqlserver)
  • Re: need help regarding finding rows modifeid during certain time frame
    ... the time period might be an option, I might be wrong here again. ... CREATE TRIGGER modified_tri ON tbl FOR INSERT, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Trigger For DateTime
    ... > I figured a trigger was the way to do this but haven't gotten the ... CREATE TRIGGER tbl_tri ON tbl FOR INSERT, ... UPDATE tbl ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Pflege von Feldern via Trigger
    ... > Create Trigger Country_housekeeping ON Country FOR INSERT, UPDATE AS ...
    (microsoft.public.de.sqlserver)
  • Re: Pflege von Feldern via Trigger
    ... Gruss Hansjörg ... >> ich habe mir dafür folgenden Trigger gemacht der seine Sache auch ... >> UPDATE Country ... > und den Trigger nur auf UPDATE ansprechen lässt ala: ...
    (microsoft.public.de.sqlserver)