CODEWITHSUNDEEP
sqloracle-databaseplsqltriggers
Python241820
Python241820

Reputation: 1527

how to keep a column updated?

I create the following tables:

create table AandB (
        code              varchar(9),
        total             number,
        CONSTRAINT pk_code PRIMARY KEY (code)
);

create table A (
        codeA              varchar(9),
        numberA              number,
        CONSTRAINT pk_codeA PRIMARY KEY (codeA)
);

create table B (
        codeB              varchar(9),
        numberB            number,
        CONSTRAINT pk_codeB PRIMARY KEY (codeB)

);

I inserted the following datas:

insert into AandB(code) values('0x1');
insert into AandB(code) values('0x2');
insert into AandB(code) values('0x3');

insert into A(codeA, numberA) values('0x1',5);
insert into A(codeA, numberA) values('0x2',6);
insert into A(codeA, numberA) values('0x3',1);

insert into B(codeB, numberB) values('0x1',8);
insert into B(codeB, numberB) values('0x2',10);
insert into B(codeB, numberB) values('0x3',12);

The question is: how to keep the table "AandB" updated with a trigger?.

I use the following query to add both tables:

UPDATE AandB ab
SET ab.total = (SELECT a.numberA + b.numberB
                FROM A
                INNER JOIN B
                 ON(a.codeA = b.codeB)
                WHERE a.codeA = ab.code)

Now, 0X1 = 13. But if I update Table A:

update A set numberA=10 where codeA='0x1';

Now, 0X1 should be 0x1=18

Upvotes: 0

Views: 54

Answers (2)

jarlh
jarlh

Reputation: 44696

Create a view instead, will always be up to date!

create view AandB (code, total) as
SELECT a.code, a.numberA + b.numberB
FROM A
INNER JOIN B ON (a.codeA = b.codeB)

Upvotes: 1

Mohammed Q. Hussain
Mohammed Q. Hussain

Reputation: 46

You could create a trigger which will be executed with each update on the column "number" of both tables "A" and "B". The body of your trigger can be same as your update statement:

UPDATE AandB ab
SET ab.total = (SELECT a.numberA + b.numberB
                FROM A
                INNER JOIN B
                 ON(a.codeA = b.codeB)
                WHERE a.codeA = ab.code)

For better performance you can add a "WHERE" clause to it as the following:

(For the trigger of table A)

WHERE ab.code = :NEW.numberA

(For the trigger of table B)

WHERE ab.code = :NEW.numberB

Upvotes: 0

Related Questions