Trigger that inserts automatic value in a record

Question

Good afternoon, guys. How are you? I’m trying to create a Rigger that when performing a partner registration it automatically marks that the partner is a client. In the database, this field is called CLIENT and receives the value’S', however, I am facing the following error when trying to register a partner :ORA-04098: 'TEST.AD_TRG_INC_UPD_TGFPAR' trigger is invalid and the revalidation failed. Could you help me?

CREATE OR REPLACE TRIGGER AD_TRG_INC_UPD_TGFPAR
BEFORE INSERT OR UPDATE OF CLIENTE ON TGFPAR
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW 
BEGIN
    IF :NEW.CLIENTE ='N' OR :NEW.CLIENTE IS NULL THEN 
       :NEW.CLIENTE := 'S';
END´´´

Answer 1

I believe you’re making a mess between UPDATE and INSERT, in case the field already exists you want to change it to’S', in case you have to use the UPDATE:

CREATE OR REPLACE TRIGGER AD_TRG_INC_UPD_TGFPAR
AFTER INSERT OR UPDATE OF CLIENTE ON TGFPAR
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW 
BEGIN
    IF :NEW.CLIENTE IS NULL THEN 
       :NEW.CLIENTE := 'S';
END

Another mistake that may be giving is that whenever you use the :NEW or :OLD the next value after . (dot) is the column name, check if it really is the column name of the value you want to change.

Answer 2

CREATE OR REPLACE TRIGGER AD_TRG_INC_UPD_TGFPAR
BEFORE INSERT OR UPDATE OF CLIENTE ON TGFPAR
FOR EACH ROW 
BEGIN
    IF TRIM(:NEW.CLIENTE) IS NULL THEN 
      :NEW.CLIENTE := 'S';
    END IF;
END;

---

You could also create a DEFAULT in the column

 ALTER TABLE TGFPAR
    MODIFY CLIENTE NOT NULL DEFAULT 'S';

Applying this to ensure there are no null values

UPDATE TGFPAR SET CLIENTE NOT NULL DEFAULT 'S'
WHERE TRIM(CLIENTE) IS NULL; 

Answer 3

Good afternoon, I appreciate the help. I was able to solve thanks to your advice. The query was like this.

BEFORE INSERT OR UPDATE ON TGFPAR
--REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION; -- corrige mutante trigger
V_CLIENTE VARCHAR2(10);
BEGIN
SELECT user INTO V_CLIENTE
FROM DUAL;

:new.cliente:= 'S';
END;´´´