ms sql server 2000 trigger, Help!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ms sql server 2000 trigger, Help!

Author

Topic

lppier
Starting Member

3 Posts

Posted - 2004-09-14 : 00:39:25

Hi

I'm trying to create a trigger in my school database project. However, the raiseerror is executed no matter what set of values I have tried.
The following are some of the data I have tried to insert into the table.
Any help would be greatly appreciated! Thanks!

Pier.

----------------------------------------------

<Query>
INSERT INTO TEAM
VALUES('Australia','Grp2(p)');

<result>
Server: Msg 50000, Level 16, State 1, Procedure Duplicate_team, Line 11
Each participating country can send only 1 team!!

<Query>
INSERT INTO TEAM
VALUES('Thailand','Grp1(p)');

<result>
Server: Msg 50000, Level 16, State 1, Procedure Duplicate_team, Line 11
Each participating country can send only 1 team!!

----------------------------------------------

<Trigger code>

CREATE TRIGGER Duplicate_team
ON TEAM
FOR INSERT
AS
IF EXISTS
(SELECT 'TRUE'
FROM Inserted i
WHERE i.Nation=Nation)

BEGIN
RAISERROR('Each participating country can send only 1 team!!',16,1)
ROLLBACK TRAN

END

----------------------------------------------

<TEAM table contents>

Nation Grp
------ ---
Australia Grp1(p)
Brazil Grp2(p)
China Grp1(p)
England Grp2(p)
France Grp1(p)
Japan Grp1(p)
Singapore Grp2(p)

Here's the table creation code, just in case.
CREATE TABLE TEAM (
Nation VARCHAR(25) NOT NULL,
Grp VARCHAR(15) NOT NULL,
PRIMARY KEY (Nation,Grp),
FOREIGN KEY (Nation) References NATIONALITY(Nation)
ON DELETE CASCADE
ON UPDATE CASCADE
);

----------------------------------------------

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2004-09-14 : 01:03:41

Hi
I think there is no need to create a Trigger for that instead you can
just make Nation as Primary key in Table Teams instead of (Nation,Grp)

lppier
Starting Member

3 Posts

Posted - 2004-09-14 : 01:36:22

Thanks for your reply, shallu1_gupta..

It is necessary to have (Nation, Grp) as a Primary key for the table because it is linked to other tables in our database using (Nation, Grp).

Pier.

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2004-09-14 : 01:59:29

I think this might help you

ALTER TRIGGER Duplicate_team
ON TEAM
INSTEAD OF INSERT
AS
IF EXISTS
(SELECT 'TRUE'
FROM Inserted i,Team t
WHERE i.Nation=t.Nation)

BEGIN
RAISERROR('Each participating country can send only 1 team!!',16,1)
ROLLBACK TRAN

END
ELSE
BEGIN

INSERT INTO TEAM
Select * from Inserted
END

lppier
Starting Member

3 Posts

Posted - 2004-09-14 : 02:14:28

Thanks a lot, it works!
Could you be so kind as to explain why

INSTEAD OF INSERT
works and
FOR INSERT
doesn't ? (I tried both cases)

I understand from my book that INSTEAD OF that the trigger is checked before the table constaints are checked, while for FOR is the other way round.

Again, thanks for your help!

Pier.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources