Problem about trigger on different table

fool1988 · #1 (**permalink**) 08-31-10, 02:31

I having some problem to do trigger on different table..
Please help. Thank.

Code:

CREATE TABLE "KHNG    "."INSTRUMENT"  (
		  "INSTRUMENT_ID" INTEGER NOT NULL , 
		  "INSTRUMENT_BRAND" VARCHAR(10) , 
		  "INSTRUMENT_TYPE" VARCHAR(10) , 
		  "INSTRUMENT_QTY" DECIMAL(5,0) NOT NULL )   
		 IN "USERSPACE1" ; 


-- DDL Statements for primary key on Table "KHNG    "."INSTRUMENT"

ALTER TABLE "KHNG    "."INSTRUMENT" 
	ADD CONSTRAINT "INSTRUMENT_PK" PRIMARY KEY
		("INSTRUMENT_ID");

Code:

CREATE TABLE "KHNG    "."RENTED"  (
		  "RENTED_DURATION" INTEGER NOT NULL , 
		  "INSTRUMENT_ID" INTEGER NOT NULL , 
		  "STAFF_ID" INTEGER NOT NULL , 
		  "CUSTOMER_ID" INTEGER NOT NULL , 
		  "DUEDATE" DATE NOT NULL )   
		 IN "USERSPACE1" ; 


-- DDL Statements for primary key on Table "KHNG    "."RENTED"

ALTER TABLE "KHNG    "."RENTED" 
	ADD CONSTRAINT "CC1283234129526" PRIMARY KEY
		("DUEDATE");

What i wan to do is..once the instrument_ID from rented table have been insert than it will minus 1 quantity to Instrument.instrument_qty.
How to do the trigger?

ARWinner · #2 (**permalink**) 08-31-10, 08:16

What have you tried so far for your trigger?

Also, you are going to have a problem with your "RENTED" table. You have a primary key on "DUEDATE". This will prevent any two instruments from being due on the same date.

Andy

Jack Vamvas · #3 (**permalink**) 08-31-10, 16:17

If possible use a stored procedure - as possibly there is performance degrading with INSERTS. Although this will depend on your specific requirements.
It is possible to complete what you need via a stored procedure

Internet Services

Web Development

Digital Marketing

Consumer Tech