Error ora 04079 invalid trigger specification



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.

Читайте также:  Python exception create own

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.

Читайте также:  Error 404 дискорд сервер

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 COMPILE command to compile the trigger. The trigger will then be in stored form. Also, you may want to review that a proper upgrade was done.

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

for each row
.(:new., :old.)
;

I’ve been looking far more into this and can see that if I user CALL before the and remove the ; at the end the trigger will be created.

What I am not clear on is why this is.

Can anyone explain?

I think you have found one of the differences between SQL and PL/SQL.
Normally, the code for a trigger is wrapped in a BEGIN/END (with a DECLARE if you have variables to declare).
Does the following version of your trigger work for you?
CREATE OR REPLACE TRIGGER
before insert or update on

for each row
BEGIN

Thanks for the suggestion. I tried wrapping it on a code block, using both interfaces (see below), but the trigger will not compile.

Originally I was using SQL tools and if I DDL the trigger then execute the statement it fails on an ‘invalid trigger specification’ error. I then fiddled with the code and got it to work by using the CALL and removing the ; in SQL tools. If I then regenerate the same DDL, SQL tools removes the CALL and re-adds the ;.

I then moved to SQL Developer and DDL the trigger from there and it has the same DDL as I input for SQL tools using the CALL and removing the ;. So SQL Developer works fine.

I am not clear as to why the SQL tools interface strips out the CALL and places the ; at the end of the statement when I DDL the trigger.

The reason I am going down this path (as you may wonder if it’s working in SQL Developer then why bother with SQL tools) is that I am testing out a third product to reverse engineering our database but it has failed to import around 1000 triggers due to this issue of the syntax error. What’s made worse is the interface has an option to rewrite the trigger with the correct synatx, but that interface doesn’t like any of the solutions I enter (including the DDL from SQL Developer!).

Any suggestions as to why the interfaces amend the DDL for triggers?

Any suggestions for tools that will reverse engineer a database creating a script in the correct dependency order?

This topic has been locked by an administrator and is no longer open for commenting.

To continue this discussion, please ask a new question.

Read these next.

Bitlocker on a server

I need to encrypt my servers. Bitlocker being the main option. While I know I can store Bitlocker keys in AD to automatically unlock domain computers, how does this work with the domain servers? My fear is one day, I’ll end up restarting both of my d.

Creating powershell script

I need help with creating PowerShell script. Im not really that good at it. We are trying to delete alot of data then move it. Before we do that we need to run a script which can do the following:• The purpose of the script is to log each user• Show what .

Snap! — Flying Boats, Metaverse Vision, Floppy Disks in 2023, Replicated Cheese

Your daily dose of tech news, in brief. Welcome to the Snap! Flashback: January 12, 1997: HAL 9000 Becomes Operational (Read more HERE.) Bonus Flashback: January 12, 2005: NASA launched «Deep Impact» (Read more HERE.) You need to hear .

Spark! Pro Series — 12 January 2023

Today in History: 1984 Pyramid mystery unearthed An international panel overseeing the restoration of the Great Pyramids in Egypt overcomes years of frustration when it abandons modern construction techniques in favor of the method employed by .

Managing confidential/PII data in a non profit environment

I am looking for some suggestions on how manage confidential and Personally Identifiable Information in our non profit environment. We run a number of grant programs that require applicants to upload tax documentation, IDs, and other financial information.

Источник

Оцените статью
toolgir.ru
Adblock
detector