Can anyone tell what is wrong with this trigger:-
I am trying to insert a record into table ALERTS
when table SALES.update='Y'
On insert I need to grab the next number from the autocounters table and then increment the autocounters table by 1.
The date of the alert is 7 days after the date of the sale.
CREATE OR REPLACE TRIGGER sales_newalert AFTER INSERT ON sales FOR EACH ROW WHEN (new.UPDATE='Y')
DECLARE
v_alert_no number(8);
v_alert_dat date;
v_sales_no number(8);
v_staff number(8);
BEGIN
select autocount_val+1 into v_alert_no from autocounters where autocount_name = 'ALERT';
v_alert_dat := :new.sales_dat + 7;
v_sales_no :=
ld.sales_no;
insert into alerts
(alert_no,com_no,con_no,staff_no,alert_dat,sales_no)
values
(v_alert_no,:new.com_no,:new.con_no,v_staff,v_alert_dat,:new.sales_no);
update autocounters set autocount_num = v_alert_no where autocount_name = 'ALERT';
END;
/
My application is refusing to save the sales record if Update='Y' and therefore not creating the alert either.
Many thanks
jcl5
I am trying to insert a record into table ALERTS
when table SALES.update='Y'
On insert I need to grab the next number from the autocounters table and then increment the autocounters table by 1.
The date of the alert is 7 days after the date of the sale.
CREATE OR REPLACE TRIGGER sales_newalert AFTER INSERT ON sales FOR EACH ROW WHEN (new.UPDATE='Y')
DECLARE
v_alert_no number(8);
v_alert_dat date;
v_sales_no number(8);
v_staff number(8);
BEGIN
select autocount_val+1 into v_alert_no from autocounters where autocount_name = 'ALERT';
v_alert_dat := :new.sales_dat + 7;
v_sales_no :=
ld.sales_no;insert into alerts
(alert_no,com_no,con_no,staff_no,alert_dat,sales_no)
values
(v_alert_no,:new.com_no,:new.con_no,v_staff,v_alert_dat,:new.sales_no);
update autocounters set autocount_num = v_alert_no where autocount_name = 'ALERT';
END;
/
My application is refusing to save the sales record if Update='Y' and therefore not creating the alert either.
Many thanks
jcl5