CODEWITHSUNDEEP
oracle-databasetriggers
Nikos Krous
Nikos Krous

Reputation: 29

Create Trigger in Oracle, in order to check two Dates

I would like to create a Trigger in Oracle, in order to check the Current Date with the Date that is stored in a specific column and if they are equal, I would like to change a boolean value on the same table. I am new to Oracle and especially on creating triggers. Any help is much appreciated.

CREATE TABLE DISCIPLINARYAUDIT 
(
  DISCIPLINARYAUDITID NUMBER(19, 0) NOT NULL 
, DISCIPLINARYAUDITTYPE VARCHAR2(255 CHAR) NOT NULL 
, REVOCATIONORCOMPLETION NUMBER(1, 0) 
, STARTDATE DATE 
, ENDDATE DATE 
)

When endDate equals with current Date, RevocationORCompletion must chenage from 0 to 1.

Thank you in advance.

Upvotes: 0

Views: 426

Answers (1)

Littlefoot
Littlefoot

Reputation: 142788

Here's an example.

Date format and today's date:

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

Sample table:

SQL> create table test (id number, specific_column date, boolean_column varchar2(10));

Table created.

SQL> insert into test values (1, trunc(sysdate), 'FALSE');

1 row created.

SQL> insert into test values (2, trunc(sysdate) - 2, 'FALSE');

1 row created.

SQL> select * from test order by id;

        ID SPECIFIC_C BOOLEAN_CO
---------- ---------- ----------
         1 04.06.2020 FALSE
         2 02.06.2020 FALSE

Trigger:

SQL> create or replace trigger trg_biu_test
  2    before insert or update on test
  3    for each row
  4  begin
  5    if :new.specific_column = trunc(sysdate) then
  6       :new.boolean_column := 'TRUE';
  7    end if;
  8  end;
  9  /

Trigger created.

Testing:

SQL> update test set id = 3 where id = 1;

1 row updated.

SQL> update test set id = 5 where id = 2;

1 row updated.

SQL> select * from test;

        ID SPECIFIC_C BOOLEAN_CO
---------- ---------- ----------
         3 04.06.2020 TRUE
         5 02.06.2020 FALSE

SQL>

Upvotes: 1

Related Questions