Help Creating a Trigger for Reoccuring Events [message #653019] |
Thu, 23 June 2016 21:10 |
|
mattfriend
Messages: 12 Registered: September 2014
|
Junior Member |
|
|
I have the following table:
CREATE TABLE "MAINTENANCE"."MAINTENANCE_REQUESTS"
( "DATE_SUBMITTED" DATE CONSTRAINT "CK_DATE_SUBMITTED_NN" NOT NULL ENABLE,
"FISCAL_YEAR" VARCHAR2(4 BYTE) DEFAULT '2016' CONSTRAINT "CK_FISCAL_YEAR_NN" NOT NULL ENABLE,
"WORK_ORDER" NUMBER(22,0) CONSTRAINT "CK_WORK_ORDER_NN" NOT NULL ENABLE,
"SUBMITTED_BY" VARCHAR2(40 BYTE),
"PHONE" VARCHAR2(14 BYTE),
"ASSIGNED_DATE" DATE,
"ASSIGNED_TO" VARCHAR2(50 BYTE),
"BUILDING_NUMBER" VARCHAR2(30 BYTE),
"ROOM_NUMBER" VARCHAR2(30 BYTE),
"PRIORITY_LEVEL" VARCHAR2(60 BYTE),
"URGENCY_LEVEL" VARCHAR2(80 BYTE),
"MAINTENANCE_ISSUE" VARCHAR2(120 BYTE) CONSTRAINT "CK_MAIN_ISSUE_NN" NOT NULL ENABLE,
"WORK_REQUIRED" VARCHAR2(2000 BYTE),
"START_DATE" DATE,
"END_DATE" DATE,
"REOCCUR" NUMBER,
"ID" NUMBER CONSTRAINT "CK_ID_MAIN_REQ_NN" NOT NULL ENABLE,
The table comes from a Maintenance Work Order project. Some Work Orders need to be able to repeat on a certain basis.
What I would like to do is have a "Reoccur" column where the maintenance work can put the number of days when the maintenance should be scheduled again (say for example they have to clean the boilers once a year).
So what I am needing help with is an insert or update trigger that IF there is a number in the REOCCUR field, will insert the original record they are filling out (or updating) AND then insert a new Work order in the same table with the number of days listed in REOCCUR added to the END_DATE. So say the enter Todays date in the END_DATE (the date they finished the work order) then I need to add the number of days to that (say 30 days, 180 days or 365 days) and insert the new record with the new START_DATE based off of the END_DATE plus the days listed in REOCCUR in x days. If there is nothing listed in the REOCCUR date then it should be skipped....
Summary:
Create a new record if based on:
1. The END_DATE plus
2. The number listed in "REOCCUR" Number
-----------
Save 1 + 2 as the START_DATE for the record that is inserted.
I hope all that makes sense...
Thank you for any help...
Matthew
|
|
|
|
|
Re: Help Creating a Trigger for Reoccuring Events [message #653032 is a reply to message #653022] |
Fri, 24 June 2016 07:09 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
A trigger can't insert into the table that the trigger fired on. Just Not Allowed. This is something that should be in the application or like John said, have a daughter table that would contain scheduling. A trigger could easily insert into a daughter table. If you have a canned application, often the only way you can alter the flow is to use triggers. However if you wrote your own application then you should have no triggers and do all the logic in the application.
[Updated on: Fri, 24 June 2016 13:23] Report message to a moderator
|
|
|
|
|
Re: Help Creating a Trigger for Reoccuring Events [message #653092 is a reply to message #653056] |
Mon, 27 June 2016 08:25 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
While I was talking about a row trigger, I am not failure with a "INSERT VALUES triggering statement", As far as I know you can only alter the current row in a row level trigger. You can by using memory arrays and a before statement, row trigger, and after statement trigger alter other rows using the row triggers values but could you give me a link to where a row level trigger can perform DML on other records in the same table. I would really be interested.
|
|
|
Re: Help Creating a Trigger for Reoccuring Events [message #653093 is a reply to message #653092] |
Mon, 27 June 2016 08:38 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
No, you can do whatever you want with triggering table in a row level trigger if triggering statement is INSERT ... VALUES:
SQL> create table tbl
2 as select level lvl from dual connect by level <= 10;
Table created.
SQL> create or replace
2 trigger tbl_bir
3 before insert
4 on tbl
5 for each row
6 begin
7 delete tbl where mod(lvl,2) = mod(:new.lvl,2);
8 end;
9 /
Trigger created.
SQL> insert
2 into tbl
3 select 11 from dual
4 /
into tbl
*
ERROR at line 2:
ORA-04091: table SCOTT.TBL is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TBL_BIR", line 2
ORA-04088: error during execution of trigger 'SCOTT.TBL_BIR'
SQL> insert
2 into tbl
3 values(11)
4 /
1 row created.
SQL> select * from tbl
2 /
LVL
----------
2
4
6
8
10
11
6 rows selected.
SQL>
SY.
|
|
|
Re: Help Creating a Trigger for Reoccuring Events [message #653094 is a reply to message #653093] |
Mon, 27 June 2016 08:43 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
And reason behind it is quite simple. Table state is defined before triggering statement and after triggering statement and is undefined in between (table is mutating). INSERT ... VALUES statement is the only case when Oracle knows for sure one and only one row is inserted and therefore before statement and before for each row match in regards to table state thus we are guaranteed table isn't mutating.
SY.
[Updated on: Mon, 27 June 2016 08:47] Report message to a moderator
|
|
|
|
|
Re: Help Creating a Trigger for Reoccuring Events [message #653099 is a reply to message #653095] |
Mon, 27 June 2016 09:21 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Unfortunately reference to such behavior was removed from documentation ages ago. Last one I could find is in 8.1.5 docs Using Triggers:
Mutating and Constraining Tables
A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or it is a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity constraint.
A constraining table is a table that a triggering statement might need to read either directly, for a SQL statement, or indirectly, for a declarative referential integrity constraint. A table is mutating or constraining only to the session that issued the statement in progress.
Tables are never considered mutating or constraining for statement triggers unless the trigger is fired as the result of a DELETE CASCADE. Views are not considered mutating or constraining in INSTEAD OF triggers.
For all row triggers, or for statement triggers that were fired as the result of a DELETE CASCADE, there are two important restrictions regarding mutating and constraining tables. These restrictions prevent a trigger from seeing an inconsistent set of data.
The SQL statements of a trigger cannot read from (query) or modify a mutating table of the triggering statement.
The statements of a trigger cannot change the PRIMARY, FOREIGN, or UNIQUE KEY columns of a constraining table of the triggering statement.
There is an exception to this restriction: For a single row INSERT, constraining tables are mutating for AFTER row triggers, but not for BEFORE row triggers. INSERT statements that involve more than one row, such as INSERT INTO Emp_tab SELECT..., are not considered single row inserts, even if they only result in one row being inserted.
SY.
|
|
|
|