ORA-04079: invalid trigger specification
CREATE OR REPLACE TRIGGER CARE.SURCHRG_UPDT
AFTER INSERT
ON CARE.CHARGE_TRANS_DETAILS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
if (SELECT pad_adm_no FROM patient_adm WHERE pad_cur_pat_type = ‘CRD’ AND pad_cur_bill_cls IN (‘SPN’, ‘SHR’)AND pad_org_cd NOT IN (‘OM6’)))
BEGIN
UPDATE charge_trans_details
SET ctd_surcharge_percent = 10,
ctd_spon_surcharge_amt = ctd_item_pat_spnr_amt * 10 / 100
WHERE ctd_grp_cd <> ‘P1’ and ctd_cancel_flag = ‘N’
END;
I am facing above error while creating this trigger.
Best Answer
Sorry, try this
CREATE OR REPLACE TRIGGER CARE.SURCHRG_UPDT
AFTER INSERT
ON CARE.CHARGE_TRANS_DETAILS
REFERENCING NEW AS NEW OLD AS OLD
declare
temp VARCHAR2 (10);
BEGIN
SELECT pad_adm_no
INTO temp
FROM patient_adm
WHERE pad_cur_pat_type = ‘CRD’
AND pad_cur_bill_cls IN (‘SPN’, ‘SHR’)
AND pad_org_cd NOT IN (‘OM6’);
IF temp IS NOT NULL
THEN
UPDATE charge_trans_details
SET ctd_surcharge_percent = 10,
ctd_spon_surcharge_amt = ctd_item_pat_spnr_amt * 10 / 100
WHERE ctd_grp_cd = ‘P1’ AND ctd_cancel_flag = ‘N’ *;*
END IF;
END;
Edited by: HuaMin Chen on May 26, 2012 3:50 PM
Edited by: HuaMin Chen on May 26, 2012 3:50 PM
Answers
Try this
CREATE OR REPLACE TRIGGER CARE.SURCHRG_UPDT
AFTER INSERT
ON CARE.CHARGE_TRANS_DETAILS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
UPDATE charge_trans_details
SET ctd_surcharge_percent = 10,
ctd_spon_surcharge_amt = ctd_item_pat_spnr_amt * 10 / 100
WHERE ctd_grp_cd ‘P1’ and ctd_cancel_flag = ‘N’
END;
by removing that if line
removing «If statement» is not the solution
i want that if condition also to satisfy then only the update statement will fire.
«If» line is with syntax error. You should have such syntax like
CREATE OR REPLACE TRIGGER CARE.SURCHRG_UPDT
AFTER INSERT
ON CARE.CHARGE_TRANS_DETAILS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
Var1 patient_adm. pad_adm_no%type;
BEGIN
SELECT pad_adm_no into var1
FROM patient_adm WHERE pad_cur_pat_type = ‘CRD’ AND pad_cur_bill_cls IN (‘SPN’, ‘SHR’)AND pad_org_cd NOT IN (‘OM6’);
if …
…
End if;
UPDATE charge_trans_details
SET ctd_surcharge_percent = 10,
ctd_spon_surcharge_amt = ctd_item_pat_spnr_amt * 10 / 100
WHERE ctd_grp_cd ‘P1’ and ctd_cancel_flag = ‘N’
END;
and see this syntax
CREATE TRIGGER hr.salary_check
BEFORE INSERT OR UPDATE OF salary, job_id ON hr.employees
FOR EACH ROW
WHEN (new.job_id <> ‘AD_VP’)
pl/sql_block
Many Thanks & Best Regards,
HuaMin
Edited by: HuaMin Chen on May 26, 2012 3:04 PM
Will you please explain me in depth.. ??
What i want to do is I am checking that condition from Patient_adm table and if those condition is true only then after it will update in Charge_trans_Detail.
I am trying mention below code also but i am facing error ORA-00936: missing expression
CREATE OR REPLACE TRIGGER CARE.SURCHRG_UPDT
AFTER INSERT
ON CARE.CHARGE_TRANS_DETAILS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
UPDATE charge_trans_details
SET ctd_surcharge_percent = 10,
ctd_spon_surcharge_amt = ctd_item_pat_spnr_amt * 10 / 100
WHERE ctd_grp_cd <> ‘P1’ and ctd_cancel_flag = ‘N’ and AND ctd_adm_no = (
SELECT pad_adm_no
FROM patient_adm
WHERE pad_cur_pat_type = ‘CRD’
AND pad_cur_bill_cls IN (‘SPN’, ‘SHR’)
AND pad_org_cd NOT IN (‘OM6’))
END;
If you want to insert based on a Condition, then it should be something like this,
Error ora 04079 invalid trigger specification
This section lists messages generated when triggers are accessed. For more trigger messages, see 25000-25099: Trigger Messages on page -358.
ORA-04070: invalid trigger name
Cause: An invalid trigger name was specified.
Action: Check that the trigger name is not a reserved keyword.
ORA-04071: missing BEFORE or AFTER keyword
Cause: The trigger statement is missing the BEFORE/AFTER clause.
Action: Specify either BEFORE or AFTER.
ORA-04072: invalid trigger type
Cause: An invalid trigger type was given.
Action: Specify either INSERT, UPDATE, or DELETE as the trigger type.
ORA-04073: column list not valid for this trigger type
Cause: A column list was specified for a trigger type other than UPDATE.
Action: Remove the column list from the trigger.
ORA-04074: invalid REFERENCING name
Cause: An invalid name was given in the REFERENCING clause.
Action: Check that the REFERENCING name is not a reserved word.
ORA-04075: invalid trigger action
Cause: A statement was given for the trigger action.
Action: Specify another trigger action that is not a statement.
ORA-04076: invalid NEW or OLD specification
Cause: An invalid NEW or OLD specification was given for a column.
Action: Respecify the column using a correct NEW or OLD specification.
ORA-04077: WHEN clause cannot be used with table level triggers
Cause: The WHEN clause can only be specified for row-level triggers.
Action: Remove the WHEN clause or specify it for each row.
ORA-04078: OLD and NEW values cannot be identical
Cause: The REFERENCING clause specifies identical values for NEW and OLD.
Action: Specify the REFERENCING clause again with a different value for either NEW or OLD.
ORA-04079: invalid trigger specification
Cause: The CREATE TRIGGER statement is invalid.
Action: Refer to Oracle8 Server SQL Reference for the correct syntax of the CREATE TRIGGER statement.
ORA-04080: trigger name does not exist
Cause: The trigger name specified is invalid, or the trigger does not exist.
Action: Check the trigger name.
ORA-04081: trigger name already exists
Cause: The trigger name or type already exists.
Action: Use a different trigger name or drop the trigger that is of the same type.
ORA-04082: NEW or OLD references not allowed in table level triggers
Cause: The trigger is accessing NEW or OLD values in a table trigger.
Action: Remove any new or old references.
ORA-04083: invalid trigger variable name
Cause: The variable referenced in the trigger body is invalid.
Action: See Oracle8 Server SQL Reference for valid trigger variable types.
ORA-04084: cannot change trigger NEW values in after row triggers
Cause: New trigger variables can only be changed in before-row triggers.
Action: Change trigger type or remove the variable reference.
ORA-04085: cannot change the value of an OLD reference variable
Cause: OLD values can only be read and not changed.
Action: Do not attempt to change an OLD variable.
ORA-04086: trigger description too long, move Comments into triggering code
Cause: The trigger description is limited to 2000 characters, for dictionary storage reasons. The description does not include the text of the «when» clause or the text of the PL/SQL code executed for the trigger.
Action: If the trigger description contains a large Comment, move that Comment into the PL/SQL code for the trigger.
ORA-04087: cannot change the value of ROWID reference variable
Cause: ROWIDs can only be read and not changed.
Action: Do not attempt to change a ROWID value.
ORA-04088: error during execution of trigger name . name
Cause: A runtime error occurred during execution of a trigger.
Action: Check the triggers that were involved in the operation.
ORA-04089: cannot create triggers on objects owned by SYS
Cause: An attempt was made to create a trigger on an object owned by SYS.
Action: Do not create triggers on objects owned by SYS.
ORA-04090: name specifies same table, event and trigger time as name
Cause: The named trigger has a duplicate event and trigger time as another trigger.
Action: Combine both triggers into one trigger.
ORA-04091: table name is mutating, trigger/function may not see it
Cause: A trigger or a user-defined PL/SQL function that is referenced in the statement attempted to query or modify a table that was in the middle of being modified by the statement that fired the trigger.
Action: Rewrite the trigger or function so it does not read the table.
ORA-04092: cannot COMMIT or ROLLBACK in a trigger
Cause: A trigger attempted to COMMIT or ROLLBACK. This is not permitted.
Action: Rewrite the trigger so that COMMIT or ROLLBACK statements are not used.
ORA-04093: references to columns of type LONG are not allowed in triggers
Cause: A trigger attempted to reference a LONG column in the triggering table.
Action: Do not reference the LONG column.
ORA-04094: table name is constraining, trigger may not modify it
Cause: A trigger attempted to modify a table that was constraining for some referential constraint of a parent SQL statement.
Action: Rewrite the trigger so that it does not modify that table.
ORA-04095: trigger name already exists on another table, cannot replace
Cause: An attempt was made to replace a trigger that exists on another table.
Action: Re-create the trigger on the other table using the CREATE OR REPLACE TRIGGER statement.
ORA-04096: trigger name has a WHEN clause that is larger than 2K
Cause: A trigger’s WHEN clause is limited to 2K for dictionary storage reasons.
Action: Use a smaller WHEN clause. Note, the trigger body could perform the same limiting action as the WHEN clause.
ORA-04097: DDL conflict while trying to drop or alter a trigger
Cause: An attempt was made to concurrently perform two DDL operations on a trigger or trigger table.
Action: Investigate the new state of the trigger and retry the DDL operation, if still appropriate.
ORA-04098: trigger name is invalid and failed re-validation
Cause: A trigger was attempted to be retrieved for execution and was found to be invalid. This also means that compilation/authorization failed for the trigger.
Action: The options are to resolve the compilation/authorization errors, disable the trigger, or drop the trigger.
For more information about enabling and disabling triggers, see the index entries on «enabling, triggers,» «disabling, triggers,» and «DROP TRIGGER command» in Oracle8 Server SQL Reference .
ORA-04099: trigger name is valid but not stored in compiled form
Cause: A trigger was attempted to be retrieved for execution and was found to be valid, but not stored. This may mean the an upgrade was done improperly from a non-stored trigger release.
Action: Execute the ALTER
For more information about ALTER TRIGGER, see the index entry on «ALTER TRIGGER» in Oracle8 Server SQL Reference .
(Error): ORA-04079: invalid trigger specification
i was creating a trigger and i had the above error message
that code below shows how i created my trigger
CREATE OR REPLACE TRIGGER TG_GL_GET_MAXIMUM_DATE
AFTER INSERT ON TABLE Al
FOR EACH ROW
(
strsen VARCHAR2,
strage VARCHAR2,
numcurcode VARCHAR2,
stracctno VARCHAR2,
dt_date DATE
)
as
strmessg VARCHAR2 (50);
num_cnt NUMBER;
dtmax_date DATE;
begin
here i perform an update and insert here
end ;
\
what could be the problem
Answers
You should really post the entire piece of code, however, your syntax is most definitely invalid. Here is the diagram for the CREATE TRIGGER statement:
As always check the documentation at: http://tahiti.oracle.com when you have questions.
The problem is twofold
— You didn’t read the documentation when creating the trigger, so you didn’t know a trigger isn’t a procedure and doesn’t accept parameters
— When you got this error, you didn’t read the online error manual, in order to resolve it.
You are kindly requested to do your own research prior to posting, and to avoid asking syntax questions and other questions for which you can easily find the answer yourself in the searchable documentation
at http://tahiti.oracle.com
—————
Sybrand Bakker
Senior Oracle DBA
Oracle Trigger ORA-04079: invalid trigger specification
Reduce Complexity & Optimize IT Capabilities
7 Replies
send the trigger code
log in into your schema
and
select trigger name form all_objects, if it exists then drop the trigger and use create or replace trigger statment.
thx
Maybe there is an extra newline from the copy and paste process.
Hi i have double checked — also dropped the trigger and tried to recreate it with the same error.
Here is the code:
CREATE OR REPLACE TRIGGER
before insert or update on
