trigger problems

hey, I have afther hitting my head into the monitors, come to the conclusion that I need help from someone,


I have a db, unfortunally it is in swedish, but anyways,

I have a table agent, and I have a heritance situation with two other tables,
faltAgent and gruppledarAgent (shown below) what I want to do is to set restrictions to the salarys of the agent table and the variable lon, lon is sallary in swedish!

the first trigger is for the gruppledarAgent (groupleaderAgent), one of these cannot have a sallary of more than 25000 and not less than 12000.

my problem is when runnin this that it doesnt work, I want in the trigger to compare the nr:s that I get from the agent table, to see if that nr is a groupleadAgent nr or not and if so, apply the changes the trigger must do,
I hope my code below can help you helping me..URL



create table agent(

namn char(1) not null,
nr integer not null,
lon integer default 13000 not null,
fnamn varchar(15) not null,
enamn varchar(15) not null,
unamn as (fnamn+' '+enamn),
/*check(nr<99), */
check(nr<13),
check(not nr=13),
check(fnamn not in('Leif Loket','Greger','Greve')),
check(enamn not in('Olsson','Puckowitz','Dracula')),
primary key(namn,nr),
);


create table faltAgent(

namn char(1) not null,
nr integer not null,
kompet varchar(30) not null,
specialitet varchar(30) not null,
check(specialitet in ('stadoperationer','infangandeoperationer')),
check(kompet in('desarmering','langdistansskytte')),
primary key(namn,nr)

)

create table gruppledarAgent(

namn char(1) not null,
nr integer not null,
primary key(namn,nr)
)

CREATE TRIGGER gruppledaragentloner ON agent INSTEAD OF INSERT AS

DECLARE @gruppledaragentLON INTEGER;
DECLARE @nr INTEGER;

DECLARE d CURSOR
FOR SELECT lon,nr FROM INSERTED where exists(select * from gruppledarAgent where nr=gruppledarAgent.nr)
OPEN d
FETCH d INTO @gruppledaragentLON,@nr
WHILE (@@FETCH_STATUS=0) BEGIN
IF (@gruppledaragentLON>25000 and @gruppledaragentLON<12000) BEGIN
RAISERROR('Agenter far inte ha loner over 25 lok eller mindre dn 12000',16,1);
END
ELSE BEGIN
INSERT INTO agent(lon,nr) VALUES(@gruppledaragentLON,@nr);
END
FETCH d INTO @gruppledaragentLON,@nr;
END
CLOSE d
DEALLOCATE d

GO

insert into agent(namn,nr,lon,enamn,fnamn) values('R',3,15000,'sdsfda','sagfdasf');
insert into gruppledarAgent(namn,nr) values('R',3);



If someone want to help me but dont really get what I mean, just icq/msn me,

35962926 / a02edves@hotmail.com


I hope someone is able to help ...bye!

Your error may be here:

IF (@gruppledaragentLON>25000 and @gruppledaragentLON<12000) BEGIN

Think of it this way, if @gruppledaraagentLON = 30000, the first part is true (i.e.) > 25000, but the second part is false ( < 12000). Since you're using AND, the entire statement is evaluated as false. I think you mean:

IF (@gruppledaragentLON>25000 OR @gruppledaragentLON<12000) BEGIN