How do I create a trigger with the below problem statement?

Question

I am trying to create a trigger named transaction_type_af_insert that is triggered whenever a new record is inserted into ransaction_type table. This trigger will insert the new type and action into the table transaction_type_log_history after the insertion of transaction type details. The action name in the affected log table transaction_type_log_history is 'After_Insert_transaction_type'.

But facing below error:

Warning: Trigger created with compilation errors.
insert into transaction_type(id,type)values(22,'Credit Card')
            *
ERROR at line 1:
ORA-04098: trigger 'P12097.TRANSACTION_TYPE_AF_INSERT' is invalid and failed
re-validation

Please help me resolve the issue

CREATE TRIGGER transaction_type_af_insert
AFTER INSERT 
ON transaction_type FOR EACH ROW
DECLARE
type varchar(30)
action varchar(30)
BEGIN
UPDATE transaction_type_log_history
SET action = 'After_Insert_transaction_type'
    WHERE transaction_type.id = transaction_type_log_history.id;
END;

Alex Poole Alex Poole · Accepted Answer · 2020-08-04T08:42:07

You can query the user_errors view to see what is actually wrong; some clients let you show errors as a shortcut after you create the object (SQL Developer, SQLcl, SQL*Plus..) and see the 'Warning: Trigger created with compilation errors' message. With your current code that will say something like:

PLS-00103: Encountered the symbol "(" when expecting one of the following:

; is default authid as force under accessible

type is a reserved word so you can't use that for a local variable name - it's expecting you to be declaring a record type. Changing the name of that variable reveals the next error:

PLS-00103: Encountered the symbol "ACTION" when expecting one of the following:

:= ; not null default character

which is because you don't have semicolons after your declarations.

Fixing that still gets 'ORA-00904: "TRANSACTION_TYPE"."ID": invalid identifier' because your update statement doesn't include the transaction_type table. You can use correlation names to refer to data in the original table row statement the trigger fired against, using :new by default; so this now compiles:

CREATE OR REPLACE TRIGGER transaction_type_af_insert
AFTER INSERT ON transaction_type
FOR EACH ROW
DECLARE
  l_type varchar2(30);
  l_action varchar2(30);
BEGIN
  UPDATE transaction_type_log_history
  SET action = 'After_Insert_transaction_type'
  WHERE transaction_type_log_history.id = :new.id;
END;
/

db<>fiddle

You aren't actually using either of the the two locally declared variables so you can remove those and the declare keyword.

You also seem to be trying to update the history table instead of inserting a new row there, which the problem statement seems to suggest you should be doing.

How do I create a trigger with the below problem statement?

1 Answers