Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2005
    Posts
    14

    Question Unanswered: Is it possible to do a ...?

    Is it possible to do a trigger on the following scenario:

    Professor have many to many relationship to department. So, It has a composite table linking both professor and department called deptassign.

    prof A work 50% in engineering and 50% in IT department. If I were to add more information on prof A 20% on economic department.

    so the total now = 50 + 50 + 20 = 120%.

    I wan to to a trigger that restrict after the user trying to insert value that are greater than 100% for a particular professor with respect to his id.

    prof_id dept_id percentage
    A 1 50
    A 2 50

    Please advise.if it possible.. i would to work on it...fyi i'm a newbie in trigger

    Thank you.
    Last edited by roustabout; 01-21-05 at 08:57.

  2. #2
    Join Date
    May 2003
    Posts
    113
    It looks more like a check-constraint for me instead of trigger. However, I doubt whether contraint can be that powerful.

    Or create an insertable view? ...and insert to view instead of table?


    Quote Originally Posted by roustabout
    Is it possible to do a trigger on the following scenario:

    Professor have many to many relationship to department. So, It has a composite table linking both professor and department called deptassign.

    prof A work 50% in engineering and 50% in IT department. If I were to add more information on prof A 20% on economic department.

    so the total now = 50 + 50 + 20 = 120%.

    I wan to to a trigger that restrict after the user trying to insert value that are greater than 100% for a particular professor with respect to his id.

    prof_id dept_id percentage
    A 1 50
    A 2 50

    Please advise.if it possible.. i would to work on it...fyi i'm a newbie in trigger

    Thank you.

  3. #3
    Join Date
    Jan 2005
    Posts
    14

    Check COnstraint

    Hi!,

    HOw to do the check constraint? HOw do i stop user from inserting more value with respect to the id.?

    Thank you very much.

  4. #4
    Join Date
    Jan 2005
    Posts
    14
    hi nidm

    do u mean like this:

    CONNECT TO PROJECT;
    ALTER TABLE DB2ADMIN.DEPT_ASSIGN ADD CONSTRAINT PERCENT CHECK (select prof_id, sum(dept_assign_percent) from dept_assign group by prof_id having sum(dept_assign_percent) > 100);
    CONNECT RESET;

    but it wont work

    Quote Originally Posted by nidm
    It looks more like a check-constraint for me instead of trigger. However, I doubt whether contraint can be that powerful.

    Or create an insertable view? ...and insert to view instead of table?

  5. #5
    Join Date
    Apr 2004
    Posts
    54
    -- Something like this:
    connect to test@

    create table professor (
    prof_id int not null,
    name char(30),
    constraint PK primary key (prof_id)
    )@

    create table dept (
    dept_id int not null,
    dept_name char(30),
    constraint PK primary key (dept_id)
    )@

    create table deptassign (
    prof_id int not null,
    dept_id int not null,
    work_prc decimal(5,2),
    constraint AtProf foreign key (prof_id)
    references professor (prof_id) on delete restrict,
    constraint AtDept foreign key (dept_id)
    references dept (dept_id) on delete restrict
    )@

    create unique index ideptassign on deptassign (prof_id,dept_id)@

    create trigger BIonDeptassign
    no cascade before insert on deptassign
    referencing new as n
    for each row
    when
    (100 < n.work_prc+( select sum(work_prc)
    from deptassign
    where deptassign.prof_id=n.prof_id
    )
    )
    signal sqlstate '75001' set message_text='Too much work'
    @

  6. #6
    Join Date
    Aug 2004
    Posts
    330
    If you take the trigger path, don't forget to add a corresponding BEFORE UPDATE trigger.

  7. #7
    Join Date
    Jan 2005
    Posts
    14

    Question

    Dear gardenman,



    Quote Originally Posted by gardenman
    -- Something like this:
    connect to test@

    create table professor (
    prof_id int not null,
    name char(30),
    constraint PK primary key (prof_id)
    )@

    create table dept (
    dept_id int not null,
    dept_name char(30),
    constraint PK primary key (dept_id)
    )@

    create table deptassign (
    prof_id int not null,
    dept_id int not null,
    work_prc decimal(5,2),
    constraint AtProf foreign key (prof_id)
    references professor (prof_id) on delete restrict,
    constraint AtDept foreign key (dept_id)
    references dept (dept_id) on delete restrict
    )@

    create unique index ideptassign on deptassign (prof_id,dept_id)@

    create trigger BIonDeptassign
    no cascade before insert on deptassign
    referencing new as n
    for each row
    when
    (100 < n.work_prc+( select sum(work_prc)
    from deptassign
    where deptassign.prof_id=n.prof_id
    )
    )
    signal sqlstate '75001' set message_text='Too much work'
    @
    is signal sqlstate a command that can b run on db2?

    thank you

  8. #8
    Join Date
    Apr 2004
    Posts
    54
    insert into dept values (1,'dept 1')
    DB20000I The SQL command completed successfully.

    insert into dept values (2,'dept 2')
    DB20000I The SQL command completed successfully.

    insert into professor values (1,'Prof')
    DB20000I The SQL command completed successfully.

    insert into deptassign values (1,1,50)
    DB20000I The SQL command completed successfully.

    insert into deptassign values (1,2,51)
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0438N Application raised error with diagnostic text: "Too much work".
    SQLSTATE=75001

Posting Permissions

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