CODEWITHSUNDEEP
sqloracle-databasefunctionplsqltriggers
Sasa
Sasa

Reputation: 23

ORACLE PLSQL Function/Trigger

I'm trying to create a FUNCTION/TRIGGER that can update the status_comunal_waste in table supply without the need of writing a query.

EDIT:

TRAFFIC (date),(input_quant),(output_quant),(status)
SUPPLY (input_quant), (output_quant),(status_comunal_waste)

After I import the DATE, INPUT, OUTPUT, STATUS data into the table TRAFFIC, with the trigger AIR the data is also imported into the table SUPPLY, with the execption of columns USER_CHANGES, DATE_CHANGES, USER_DELETE, DATE_DELETE as to mimic the changes the "user" has made if the "user" decides to change or add the data.

So far I've tried something like this

create or replace TRIGGER supply_after_insert
after INSERT
ON traffic
FOR EACH ROW

DECLARE

Status_comunal_waste VARCHAR2(20);

BEGIN

SELECT SUM(input_quant) - SUM(output_quant)
into Status_comunal_waste
  FROM traffic
  WHERE (key_num_code =200101);

INSERT INTO supply
(Status_comunal_waste) VALUES (Status_comunal_waste);
 END;

EDIT:

Meanwhile I switched from creating a trigger to function, and this is written so far:

create or replace 
FUNCTION   stat_comunal_waste (key_num_code number)

return number 

is

status_comunal_waste NUMBER;

BEGIN
if (key_num_code = 200101) THEN

SELECT SUM (input_quant) -  SUM (output_quant)
into status_comunal_waste
  FROM traffic
  WHERE key_num_code =200101;
  end if;

  return status_comunal_waste;
 END;

The function compiled with no errors but now I get errors with the PACKAGE

CREATE OR REPLACE PACKAGE Paket
IS
function stat_comunal_waste (key_num_code number)
return status_comunal_waste;
end Paket;
/

Error(3,1): PL/SQL: Declaration ignored

Error(4,8): PLS-00488: 'status_comunal_waste' must be a type

Upvotes: 0

Views: 1231

Answers (1)

Jag
Jag

Reputation: 291

This query might be wrong. 

SELECT SUM(input_quant) - SUM(output_quant) into Status_comunal_waste   
FROM traffic   WHERE key_num_code =200101);

Make sure you are getting the correct values. Try to replace the above query with code below and give it a try

select SUM(input_quant) into input_q FROM traffic WHERE key_num_code =200101;
select SUM(output_quant) into output_q FROM traffic WHERE key_num_code =200101;
diff := input_q  - output_q;

Final insert will be like:

INSERT INTO status (Status_comunal_waste) VALUES (diff);

Upvotes: 1

Related Questions