gritscbs
asked on
Warning: Trigger created with compilation errors. Oracle 10g SQL plus
Please help,
I am attempting to create a trigger to fire a procedure but when I create the trigger I get:
Warning: Trigger created with compilation errors.
The procedure and trigger are below (the procedure created with no errors).
-----
create or replace procedure transdtl_to_item(id2 number) as
pragma autonomous_transaction;
begin
INSERT INTO PROJECT_FILE_ITEMS (PROJECT_ID, PROJECT_FILE_ID, COST_CODE, DESCRIPTION, DATE_RECEIVED, COST_PER_UNIT, AUTHORIZED_BY, DATE_ORDERED, CURRENTLY_ASSIGNED_TO, NOTES, VENDOR_NAME, VENDOR_ID) SELECT PROJECT_ID, TRANS_ID, TRANS_DETAIL_ID, DESCRIPTION, TRANS_DATE, (HOURS * COST_PER_HOUR) + MEAL_COST + OTHER_COST, AUTHORIZED_BY, CREATEDATE, DRIVER, PUBLIC_NOTES, VENDOR, VENDOR_NUMBER FROM TRANS_DETAIL WHERE TRANS_DETAIL_ID = id2;
commit;
end;
--------
CREATE OR REPLACE
TRIGGER TRANS_TO_ITEM
AFTER UPDATE OF COMPLETE_FOR_BILLING ON TRANS_DETAIL
REFERENCING OLD AS ID3 NEW AS ID4
FOR EACH ROW
WHEN (ID4.complete_for_billing = 'Invoice Via Project')
BEGIN
transdtl_to_item(:id2.tran
END;
I am attempting to create a trigger to fire a procedure but when I create the trigger I get:
Warning: Trigger created with compilation errors.
The procedure and trigger are below (the procedure created with no errors).
-----
create or replace procedure transdtl_to_item(id2 number) as
pragma autonomous_transaction;
begin
INSERT INTO PROJECT_FILE_ITEMS (PROJECT_ID, PROJECT_FILE_ID, COST_CODE, DESCRIPTION, DATE_RECEIVED, COST_PER_UNIT, AUTHORIZED_BY, DATE_ORDERED, CURRENTLY_ASSIGNED_TO, NOTES, VENDOR_NAME, VENDOR_ID) SELECT PROJECT_ID, TRANS_ID, TRANS_DETAIL_ID, DESCRIPTION, TRANS_DATE, (HOURS * COST_PER_HOUR) + MEAL_COST + OTHER_COST, AUTHORIZED_BY, CREATEDATE, DRIVER, PUBLIC_NOTES, VENDOR, VENDOR_NUMBER FROM TRANS_DETAIL WHERE TRANS_DETAIL_ID = id2;
commit;
end;
--------
CREATE OR REPLACE
TRIGGER TRANS_TO_ITEM
AFTER UPDATE OF COMPLETE_FOR_BILLING ON TRANS_DETAIL
REFERENCING OLD AS ID3 NEW AS ID4
FOR EACH ROW
WHEN (ID4.complete_for_billing = 'Invoice Via Project')
BEGIN
transdtl_to_item(:id2.tran
END;
Last Comment
Zilvinas Zukauskas
you can use insert into PROJECT_FILE_ITEMS statement in TRANS_TO_ITEM trigger, why you are using procedure?
ASKER
As I understand it the procedure stores the record key from the source table as id2 so that the insert command inserts data from the correct source table row.
Where is the ID2 coming from?
CREATE OR REPLACE
TRIGGER TRANS_TO_ITEM
AFTER UPDATE OF COMPLETE_FOR_BILLING ON TRANS_DETAIL
REFERENCING OLD AS ID3 NEW AS ID4
FOR EACH ROW
WHEN (ID4.complete_for_billing = 'Invoice Via Project')
BEGIN
transdtl_to_item(:id2.tran
END;
Should be ID3 or ID4, depending on what value you're passing:
CREATE OR REPLACE
TRIGGER TRANS_TO_ITEM
AFTER UPDATE OF COMPLETE_FOR_BILLING ON TRANS_DETAIL
REFERENCING OLD AS ID3 NEW AS ID4
FOR EACH ROW
WHEN (ID4.complete_for_billing = 'Invoice Via Project')
BEGIN
transdtl_to_item(:ID3.tran
END;
CREATE OR REPLACE
TRIGGER TRANS_TO_ITEM
AFTER UPDATE OF COMPLETE_FOR_BILLING ON TRANS_DETAIL
REFERENCING OLD AS ID3 NEW AS ID4
FOR EACH ROW
WHEN (ID4.complete_for_billing = 'Invoice Via Project')
BEGIN
transdtl_to_item(:id2.tran
END;
Should be ID3 or ID4, depending on what value you're passing:
CREATE OR REPLACE
TRIGGER TRANS_TO_ITEM
AFTER UPDATE OF COMPLETE_FOR_BILLING ON TRANS_DETAIL
REFERENCING OLD AS ID3 NEW AS ID4
FOR EACH ROW
WHEN (ID4.complete_for_billing = 'Invoice Via Project')
BEGIN
transdtl_to_item(:ID3.tran
END;
In SQL*Plus you can also type the "SHOW ERRORS" command after the "Warning: Trigger created with compilation error" message and see what exactly is wrong with it.
ASKER
The solution was:
create or replace TRIGGER "TRANSDTL_TO_ITEM" after update of complete_for_billing on trans_detail for each row WHEN(new.complete_for_bill
end;
Thanks for the input and also the tip on SHOW ERRORS.
create or replace TRIGGER "TRANSDTL_TO_ITEM" after update of complete_for_billing on trans_detail for each row WHEN(new.complete_for_bill
end;
Thanks for the input and also the tip on SHOW ERRORS.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Oracle Database
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.
81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
