SQL HELP

[teknikalbody]

Hi,

If I have a table 'INPUT' made of the cols ex. ID, FLAG, FLAG2, DATE1, DATE2, DATE3, DATE4, TRIGGAR etc etc. then how can I update the TRIGGAR col to produce a resultant table 'OUTPUT' based on some criteria on the other 'INPUT' cols. Ex. if DATE1 > DATE2 then TRIGGAR = 1, but the triky bit is that if for the same record FLAG2 = 7 then TRIGGAR = 8 then this also needs to included in the 'OUTPUT' ... in short one record can be a number of TRIGGAR combinations that all need to be included in the 'OUTPUT'.

Im sorry if I havnt xplained this well, but please help I am really stuck with this.

TIA

 

[ManjulaMadhu]

Hi,

If the conditions are mutually exclusive, then you can write a trigger on the "input" table, which will insert the updated values of the input columns in the output table.

 

[ManjulaMadhu]

Hi,

Whatever the conditions, you can still write the trigger on the input table so that whenever there is a change in the column "trigger" , it will insert a new row in the output table.

 

[teknikalbody]

Manjulamadhu Hi,

I see wat your saying ....will i need to use something like INSERT INTO OUTPUT (....) SELECT (....) WHERE FLAG=7, then separate SQL for INSERT INTO OUTPUT (....) WHERE DATE1>DATE2.

If so how can update the TRIGGAR as i am going along ?

 

[ManjulaMadhu]

Once you write a trigger on a table(input), any updation or insert on this table, will result in inserting/updating rows in another table.
you need not specify insert statement with condition explicitly .

For eg:

CREATE OR REPLACE TRIGGER <trigger_on_insert> AFTER INSERT ON TABLET1 FOR EACH ROW
BEGIN
INSERT INTO OUTPUT NEW.TRIGGAR,..);
END;

this will insert a new row in output table whenever a new record is inserted in input table.

 

[ManjulaMadhu]

missed out the update part in the trigger


CREATE OR REPLACE TRIGGER <trigger_update_insert>
BEFORE INSERT OR UPDATE ON input
FOR EACH ROW
begin

if updating then
your code......
end if;
if inserting then
code.......
end if;
end;

 

[ManjulaMadhu]

missed out the update part in the trigger


CREATE OR REPLACE TRIGGER <trigger_update_insert>
AFTER INSERT OR UPDATE ON input
FOR EACH ROW
begin

if updating then
your code......
end if;
if inserting then
code.......
end if;
end;

 

[teknikalbody]

MM,

That worked like a dream.

thanking you.