Home » SQL & PL/SQL » SQL & PL/SQL » Need a trigger or similar thing (Oracle Database version 19.12.0.0.0)
Need a trigger or similar thing [message #686491] Mon, 26 September 2022 16:27 Go to next message
Only-Oracle
Messages: 53
Registered: June 2009
Member
Hi there,

I would like someone to help me in achieving and creating trigger, actually I didn't study PLSQL so just only I know SQL, so that's why I'm seeking someone to support me please. Actually I have no idea about PLSQL even about Scheduling a job or task daily, I was thinking of a trigger also it can be a function or a procedure or a package, actually I have no idea about PLSQL at all.

Regarding using the Virtual Column actually the system that I have is from Oracle that has interface UI coming with its database tables and its fields which cannot be changed at all otherwise I will destroy the architecture. so I cannot touch the database tables by adding new fields but I can create a trigger or function to watch specific table and update its value. I have Oracle Database version 19.12.0.0.0

And I have a table as following:
CREATE TABLE PRODUCT(
ID NUMBER,
PRODUCT_EXPIRY DATE,
PRODUCT_EXPIRY_STATUS VARCHAR2(50),
PERIOD NUMBER);

The data value will be as following example:
INSERT INTO PRODUCT(id, product_expiry, product_expiry_status, period) values(1, date'2022-10-30','Not-Expired',45);

The update statement that go into a trigger will be like this:
UPDATE product
SET product_expiry_status = 'Expired'
WHERE period >= 40;

I want something that will watch the insert query and if the field (Period) >= there should be something like trigger after inserting, change the product_expiry_status to (Expired).

The same thing I want another statement you can call it scheduler to run daily and watch the date of the field (PRODUCT_EXPIRY), if it matches the day that is coming it should change the value of (product_expiry_status) to (Expired).


Thank you very much, and totally appreciate your help and support.
Re: Need a trigger or similar thing [message #686492 is a reply to message #686491] Tue, 27 September 2022 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68315
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Trigger:
SQL> CREATE OR REPLACE TRIGGER product_bi BEFORE INSERT ON product FOR EACH ROW
  2  BEGIN
  3    IF :NEW.period >= 40 THEN
  4     :NEW.product_expiry_status := 'Expired';
  5    END IF;
  6  END;
  7  /

Trigger created.

SQL> INSERT INTO PRODUCT(id, product_expiry, product_expiry_status, period) values(1, date'2022-10-30','Not-Expired',45);

1 row created.

SQL> select * from product;
        ID PRODUCT_EXPIRY      PRODUCT_EXPIRY_STATUS                                  PERIOD
---------- ------------------- -------------------------------------------------- ----------
         1 30/10/2022 00:00:00 Expired                                                    45

1 row selected.
Job which is executed each day at 00:00 (see DBMS_JOB, "job_queue_processes" parameter must be > 0):
SQL> VAR j NUMBER
SQL> BEGIN
  2    dbms_job.submit (
  3      :j,
  4      'UPDATE product SET product_expiry_status = ''Expired'' WHERE period >= 40 or trunc(product_expiry) <= trunc(sysdate);',
  5      trunc(sysdate+1),
  6      'trunc(sysdate+1)'
  7    );
  8    COMMIT;
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> -- execute it now to check if it works
SQL> UPDATE product set product_expiry_status = 'Test';

1 row updated.

SQL> SELECT * FROM product;
        ID PRODUCT_EXPIRY      PRODUCT_EXPIRY_STATUS                                  PERIOD
---------- ------------------- -------------------------------------------------- ----------
         1 30/10/2022 00:00:00 Test                                                       45

1 row selected.

SQL> EXEC dbms_job.run(:j);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM product;
        ID PRODUCT_EXPIRY      PRODUCT_EXPIRY_STATUS                                  PERIOD
---------- ------------------- -------------------------------------------------- ----------
         1 30/10/2022 00:00:00 Expired                                                    45

1 row selected.

[Updated on: Tue, 27 September 2022 01:08]

Report message to a moderator

Re: Need a trigger or similar thing [message #686510 is a reply to message #686491] Fri, 30 September 2022 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68315
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any feedback?

Re: Need a trigger or similar thing [message #686522 is a reply to message #686510] Sat, 01 October 2022 16:42 Go to previous messageGo to next message
Only-Oracle
Messages: 53
Registered: June 2009
Member
Hi Michel,

good day, first of all, I'm sorry for late reply and I would like to thank you very much for all your help and support and efforts, really totally appreciated.

Actually the first code for the trigger worked as a charm and perfect.

the second code which is this one
SQL> BEGIN
      dbms_job.submit (
        :j,
        'UPDATE product SET product_expiry_status = ''Expired'' WHERE period >= 40 or trunc(product_expiry) <= trunc(sysdate);',
        trunc(sysdate+1),
        'trunc(sysdate+1)'
      );
      COMMIT;
    END;
 /
so when I execute it with this script
EXEC dbms_job.run(:j);
it returns back with an error which don't know why,
Error starting at line : 5 in command -
BEGIN dbms_job.run(:j); END;
Error report -
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.
So could you please elaborate and tell me what I need to do. Thanks a lot.

[Updated on: Sat, 01 October 2022 16:42]

Report message to a moderator

Re: Need a trigger or similar thing [message #686523 is a reply to message #686522] Mon, 03 October 2022 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68315
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What I posted is just an example, "j" variable, created with the statement "VAR j NUMBER", must be in the same SQL*Plus session when you create and execute the job and it must be a NUMBER.
If you don't use SQL*Plus, you have to be sure you do it in the same way.

Another way is to print/display the variable ("print j" in SQL*Plus) after "dbms_job.submit" and use the returned value in "dbms_job.run" call.

Note that the interactive execution of the job is not necessary, it is there to verify and show you the job does what it has to do.
In real case, the job is automatically executed by Oracle if "job_queue_processes" parameter is > 0.

Re: Need a trigger or similar thing [message #686546 is a reply to message #686523] Fri, 07 October 2022 22:42 Go to previous message
Only-Oracle
Messages: 53
Registered: June 2009
Member
Thanks Michel for your all efforts, totally appreciated. you are really so brilliant.
Previous Topic: trigger executing a procedure to execute dbms_stats.gather_table_stats
Next Topic: xml generate from db on redaction column
Goto Forum:
  


Current Time: Sat Dec 03 16:42:12 CST 2022