CODEWITHSUNDEEP
oracle-databaseplsqltriggers
Christian
Christian

Reputation: 1

Need to create a trigger that updates without adding same number

I need to create a trigger that activates after i make an update in Table A, registering in an audit log the number that i updated in Table A, but if the number has already been added (example the trigger tries to add 1 when there is a 1 already)it must ignore it and only let the first one.

Example:

The trigger i have already:

CREATE OR REPLACE TRIGGER registro_aeropuerto
AFTER UPDATE ON AEROPUERTO
FOR EACH ROW
DECLARE
A INT;
B INT;
BEGIN 
A := table_A_updated_column_value;
SELECT CASE
WHEN EXISTS(SELECT * FROM Audit_log WHERE A = Coordinator)
THEN 1
ELSE 0
END INTO B FROM DUAL;
IF B = 0
THEN 
INSERT INTO Audit_log(Coodinator, Date) VALUES (A, trunc(sysdate));
END;

Whenever i try to execute the trigger it gives me the next error:

The symbol ";" has been found when the it was expected:

Upvotes: 0

Views: 29

Answers (1)

Littlefoot
Littlefoot

Reputation: 142733

Sample tables (upd_col_value is column you're updating; you named it "table_A_updated_column_value")

SQL> create table aeropuerto (upd_col_value number);

Table created.

SQL> create table audit_log (coordinator number, datum date);

Table created.

Trigger can be simplified; no need to declare any additional variables nor to check first and insert next; do it in the same select statement:

SQL> create or replace trigger registro_aeropuerto
  2    after update on aeropuerto
  3    for each row
  4  begin
  5    insert into audit_log (coordinator, datum)
  6      select :new.upd_col_value, sysdate
  7      from dual
  8      where not exists (select null
  9                        from audit_log a
 10                        where a.coordinator = :new.upd_col_value
 11                       );
 12  end;
 13  /

Trigger created.

Testing:

SQL> insert into aeropuerto (upd_col_value) values (1);

1 row created.

SQL> select * from audit_log;

no rows selected

There's nothing in the log because nothing was updated. So, let's update it:

SQL> update aeropuerto set upd_col_value = 5;

1 row updated.

SQL> select * from audit_log;

COORDINATOR DATUM
----------- -------------------
          5 15.09.2021 07:14:46

SQL>

OK; log now contains a row. Another update:

SQL> update aeropuerto set upd_col_value = 6;

1 row updated.

SQL> select * from audit_log;

COORDINATOR DATUM
----------- -------------------
          5 15.09.2021 07:14:46
          6 15.09.2021 07:15:37

SQL>

Right; two rows, as 5 was updated to 6. What happens if we update 6 back to 5?

SQL> update aeropuerto set upd_col_value = 5;

1 row updated.

SQL> select * from audit_log;

COORDINATOR DATUM
----------- -------------------
          5 15.09.2021 07:14:46
          6 15.09.2021 07:15:37

SQL>

Nothing happened; row with coordinator = 5 was in the table already so new row wasn't added.

Upvotes: 1

Related Questions