# Thread: Is it possible to do a trigger...?

1. Registered User
Join Date
Jan 2005
Posts
14

## 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. Registered User
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?

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. Registered User
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. Registered User
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

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. Registered User
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. Registered User
Join Date
Aug 2004
Posts
330
If you take the trigger path, don't forget to add a corresponding BEFORE UPDATE trigger.

7. Registered User
Join Date
Jan 2005
Posts
14
Dear gardenman,

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. Registered User
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
•