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.

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?

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.

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

-- 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'
@

If you take the trigger path, don't forget to add a corresponding BEFORE UPDATE trigger.

Dear gardenman,



is signal sqlstate a command that can b run on db2?

thank you

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