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 |
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 |
|
Michel Cadot
Messages: 68665 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 #686522 is a reply to message #686510] |
Sat, 01 October 2022 16:42 |
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
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
|
|
|
|
|
Goto Forum:
Current Time: Sun Jun 30 00:17:55 CDT 2024
|