Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    1

    Unanswered: 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..



    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please confirm you have microsoft sql server, not mysql

    i'm a forum moderator and i will be happy to move your question to the microsoft sql server forum where there are more people who might be able to help you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2004
    Location
    Mars
    Posts
    137

    Thumbs up

    MySQL is yet to implement triggers.... I think u r talking abt SQL Server rather than MySQL.

    As ruddy pointed out, better post this question in MS SQL Server forum
    Sudar

    --
    My Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •