Trække en halv time fra en dato

en halv time er en dag over 24 timer over 2 halve timer på en time

sysdate-(1/(24*2)) er for en halv time siden

Skal det være mere detaljeret?

Nej det er helt fint og det virker - tak.

Jeg har dog et andet lille problem.

Førnævnte poster er nogle jeg SKAL hente informationer fra når en rowtrigger fyrer. Problemet er bare at informationerne hentes fra samme tabel som triggeren er tilknyttet og så har vi 'Mutating table'. Antallet af poster kan være fra 1 til 10.

Er der nogen som kan i se en løsning?

Triggere har en tendens til at give en "Mutating tables" bedsked når der er en select involveret.

Forklaring og løsning fra Metalink:

ORACLE MUTATING TABLE PROBLEM
=============================

This paper and the associated programs are offered to the public domain for
enhancement and research by an Oracle customer, Arup Nanda. No warranty is
either expressed or implied.  The end user is solely responsible for results
of the execution of the programs.


PROBLEM:
-------

It is a pretty common problem in Oracle that happens and frustrates many
application developers and DBAs as the application developers bug them to find
a solution to it.  It happens when a trigger on a table tries to insert,
update, or even select the table of whose trigger is being executed. Sometimes
the inability to see the table causes standstill in the development. This
paper and the associated scripts attempt to present a work around for the
problem. The work around is not the best one nor the author claims it to be
an elegant one, but it provides a solution that will be applicable in most
cases.  The approach is described by a case study.


SCENARIO
--------

The mutating table problem comes when the trigger tries to select or modify
any row of the same table. This situation comes when a table preserves some
effective date.

To describe this method, I will be using the table SALGRADE.

The table SALGRADE contains information on salary limits for each grade. The
salary limits are also based on a time factor, i.e. the employees' salary is
determined by checking which grade level was effective when they joined or
reviewed, not necessarily the grade effective now.

So the table looks like this:

SQL> desc salgrade;
Name                            Null?    Type
------------------------------- -------- ----
GRADE                                    NUMBER
LOSAL                                    NUMBER
HISAL                                    NUMBER
START_DATE                              DATE
END_DATE                                DATE

The table data looks like:

GRADE  LOSAL  HISAL  START_DATE      END_DATE
------- ------- ------- ----------      ---------
1      1000    2000    1-APR-94        3-AUG-95
1      1200    2200    3-AUG-95                  <---- Null
2      1500    3000    23-JUL-92        12-DEC-93
2      1600    3200    12-dec-93        11-jan-95
2      1800    3400    11-JAN-95                  <---- Null

This means the effective salary range of Grade 1 now is (1200-2200) nut the
employees who had review between 1-APR-94 to 3-AUG-95 will be in the range
(1000-2000). This is a purely hypothetical scenario.  Our objective is to
devise a trigger that does the following when a new record is inserted:

(1) Integrity checking for overlapping dates, i.e. the new record can't have a
    start date that is already covered.

(2) Update the record for the current grade to make the end_date equal to the
    start date of the new record (the new record's end_date must be null as
    that is the current record).

In both cases the table SALGRADE has to be selected and updated on the after
insert row trigger on the same table.  But the table will be mutating when the
trigger fires and thus a run-time error will occur.

For the first requirement, consider the following trigger:

create or replace trigger taiudr_salgrade
after insert on salgrade
for each  row
declare
        hold_found      varchar2(1);
begin
        select 'Y' into hold_found
        from salgrade
        where grade = :new.grade
        and end_date is null
        and start_date > :new.start_date;

exception
        when NO_DATA_FOUND then
                raise_application_error(-20000,'Overlapping Dates');
end;
/

Although the trigger can be created with no errors, when a user tries to
insert into the table he will receive the mutating table error:

SQL> insert into salgrade values (2, 9000, 100000, '25-dec-95', null);
insert into salgrade values (2, 9000, 100000, '25-dec-93', null)
            *
ERROR at line 1:
ORA-04091: table JACK.SALGRADE is mutating, trigger/function may not see it
ORA-06512: at line 4
ORA-04088: error during execution of trigger 'JACK.TAIUDR_SALGRADE'

SOLUTION
--------

The following approach is another possibility for the task:

1.  Create a package "SALGRADE_PKG" that contains PL/SQL tables for holding the
    SALGRADE data. Here we create 3 tables - one for holding start_dates, one
    for end_dates, and one for holding the change_flag that identifies the
    updated row.

2.  Create a BEFORE INSERT STATEMENT trigger that populates the PL/SQL table
    with the start dates, end_dates and changed_grades flag ('N').

3.  Create an AFTER INSERT ROW trigger that compares the newly inserted row
    against this PL/SQL table not the Database table.  This way the integrity
    check can be done.  The same trigger should assign the new end_date value
    to the PL/SQL table and update the value of the flag to indicate that this
    has to be changed.

4.  Create a AFTER INSERT STATEMENT trigger to update the SALGRADE table with
    the values in the PL/SQL table after looking at the change flag.

All these programs can be created by the sources found below.  I urge you to
test them and make any enhancements to them as you find necessary.


CODE:
----

Code to create test table and populate it with data:

drop table salgrade;

CREATE TABLE SALGRADE
      (GRADE NUMBER,
        LOSAL NUMBER,
        HISAL NUMBER,
        START_DATE DATE,
        END_DATE DATE);

INSERT INTO SALGRADE VALUES (1,1000,2000, '1-apr-94', '3-aug-95');
INSERT INTO SALGRADE VALUES (1,1200,2200, '3-aug-95', null);
INSERT INTO SALGRADE VALUES (2,1500,3000, '23-Jul-92', '12-dec-93');
INSERT INTO SALGRADE VALUES (2,1600,3200, '12-dec-93', '11-jan-95');
INSERT INTO SALGRADE VALUES (2,1800,3400, '11-jan-95', null);

Code for package STEP 1 above:

create or replace package salgrade_pkg as
type datetabtype        is table of date index by binary_integer;
type chartabtype        is table of char(1) index by binary_integer;
type rowidtabtype  is table of rowid  index by binary_integer;
start_date_tab          datetabtype;
end_date_tab            datetabtype;
rowid_tab                      rowidtabtype;
changed_grade          chartabtype;
start_date_tab_size    binary_integer;
end;
/

Code for before insert statement trigger STEP 2 above:

create or replace trigger tbiuds_salgrade
before insert on salgrade
declare
hold_start_date  date;
hold_end_date  date;
hold_rowid  rowid;
hold_grade  binary_integer;
cursor start_date_cur is
  select rowid, grade, start_date
  from salgrade
  where end_date is null
  order by grade;
begin
open start_date_cur;
loop
  fetch start_date_cur into
  hold_rowid, hold_grade, hold_start_date;
  exit when start_date_cur%notfound;
  salgrade_pkg.start_date_tab(hold_grade) := hold_start_date;
  salgrade_pkg.end_date_tab(hold_grade) := hold_end_date;
  salgrade_pkg.rowid_tab(hold_grade) := hold_rowid;
  salgrade_pkg.changed_grade(hold_grade) := 'N';
end loop;
salgrade_pkg.start_date_tab_size := hold_grade;
close start_date_cur;
end;
/

Code for after insert row trigger STEP 3 above:

create or replace trigger taiudr_salgrade
after insert on salgrade
for each  row
begin
if (:new.grade <= salgrade_pkg.start_date_tab_size) then
  if salgrade_pkg.start_date_tab(:new.grade)
    > :new.start_date then
  raise_application_error(-20001,'Overlapping Dates');
  end if;
  salgrade_pkg.end_date_tab(:new.grade) := :new.start_date;
  salgrade_pkg.changed_grade(:new.grade) := 'Y';
else
  salgrade_pkg.start_date_tab(:new.grade) := :new.start_date;
  salgrade_pkg.end_date_tab(:new.grade) := :new.end_date;
  salgrade_pkg.changed_grade(:new.grade) := 'N';
  salgrade_pkg.start_date_tab_size :=
    salgrade_pkg.start_date_tab_size + 1;
end if;
end;
/

Code for after insert statement trigger STEP 4 above:

create or replace trigger taiuds_salgrade
after insert on salgrade
begin
        for i in 1..(salgrade_pkg.start_date_tab_size) loop
                if (salgrade_pkg.changed_grade(i) = 'Y') then
                        update salgrade
                        set end_date = salgrade_pkg.end_date_tab(i)
                        where rowid = salgrade_pkg.rowid_tab(i);
                end if;
        end loop;
end;
/

Once all the package and triggers are created, the same insert doesn't succeed
but gives the user the a more appropriate error:

SQL> insert into salgrade values (2, 9000, 100000, '25-dec-93', null);
insert into salgrade values (2, 9000, 100000, '25-dec-93', null)
            *
ERROR at line 1:
ORA-20001: Overlapping Dates
ORA-06512: at line 5
ORA-04088: error during execution of trigger 'JACK.TAIUDR_SALGRADE'

CONCLUSION:
----------

This method is not submitted as a solution to all cases, however, it is
provided as one possibility.  The test and code are provided so modifications
can be made to suit the individual cases.  Be sure to test this completely
before relying on it's functionality.


Keywords:
=========

MUTATING TABLE, TRIGGER, PROCEDURE, ORA-4091

---------------------------------------------------------------------------
                                          Oracle WorldWide Customer Support
.

Idéen er at man flytter triggeren fra rowlevel til at være for alle records på én gang, ved at kopiere data over i pl/sql table og loope igennem.

Her er et ex. på en trigger der ikke fejler med mutating table


drop table test;
create table test (nr1 number, nr2 number);

create trigger test_in before insert on test
for each row
declare
  Mutating_table  EXCEPTION;
  PRAGMA          EXCEPTION_INIT (Mutating_table, -4091);
begin
  if :new.nr2 is null then
    insert into test (nr1, nr2) values (:new.nr1+1,:new.nr1);
  end if;
EXCEPTION
  WHEN Mutating_table THEN
      NULL;
end;
/

insert into test (nr1, nr2) values (1,null);

denne vil give to rækker i tabellen

1,NULL  og
2,1

insert i triggeren kan også være et select statement

EXCEPTION
  WHEN Mutating_table THEN
      NULL;


coily => Hvordan kan dette afhjælpe at muteringen sker? Du fanger jo bare situationen og sørger for at intet bliver opdateret.