1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Trigger after logon help please

Discussion in 'SQL PL/SQL' started by olddog, Oct 7, 2010.

  1. olddog

    olddog Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Hi all
    I'm new to Oracle and really enjoying it but I need help with some logic if anyone can help.
    Problem:
    On logon I need to select orders from orders table that are older than 10 days old and use a trigger to insert these orders into a new table I have created old_orders, code as follows inserts all records into the old_orders table what is wrong with my logic?

    create or replace TRIGGER logon_update
    AFTER logon ON SCHEMA
    BEGIN
    insert into old_orders (select order#, customer# from orders where orderdate > orderdate + 10 );
    END;

    any help constructive help greatly appreciated

    cheers
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO

    Which schema? The trigger should be coded as:

    AFTER logon on <some user>.SCHEMA

    For example if there is a user BLEEBO who owns these tables you need to insert into then the trigger should be:

    Code (SQL):
     
    CREATE OR REPLACE TRIGGER logon_update
    AFTER logon ON bleebo.SCHEMA
    BEGIN
    INSERT INTO old_orders (SELECT ORDER#, customer# FROM orders WHERE orderdate > orderdate + 10 );
    END;
    /
     
     
  3. olddog

    olddog Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Thanks for the quick reply, I left name.SCHEMA out as its the only schema i have the trigger works as it populates the old_orders table, to test if it was working correctly I updated the orders table and changed an orderdate to yesterdays date but the trigger included that row as well, so I'm guessing there is something wrong with the way I'm filtering the dates to only select dates that are older than 10 days. I tried "WHERE orderdate > SYSDATE - orderdate + 10" but that threw and error " expected Date and Got Number"
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Try this:

    where orderdate > trunc(sysdate) - 10

    to give you a solid reference date.
     
    olddog likes this.
  5. olddog

    olddog Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Thanks Zargon
    Your suggestion works when i do a select query on the DB but in the trigger it still returns all dates including the altered date (04/OCT/10), I would have thought that id the sect query works it would still work in the trigger *shruggs
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You may need to make the trigger use an autonomous transaction:

    Code (SQL):
     
    CREATE OR REPLACE TRIGGER logon_update
    AFTER logon ON SCHEMA
    DECLARE
         pragma autonomous_transaction;
    BEGIN
    INSERT INTO old_orders (SELECT ORDER#, customer# FROM orders WHERE orderdate > orderdate + 10 );
    END;
    /
     
     
  7. olddog

    olddog Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    I cant figure whats wrong with the script it looks ok, when i run the SELECT ORDER#, customer# FROM orders WHERE orderdate > orderdate + 10 ; query I get the expected result. But when i run the trigger script it seems to ignore the "where" clause and inserts all records into the old_orders table.
     
  8. olddog

    olddog Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    cheers zargon
    I got it working with your help final code is :
    create or replace
    TRIGGER logon_update
    AFTER logon ON paul.SCHEMA
    BEGIN
    INSERT INTO old_orders (select order#, customer# from orders where orderdate < trunc(sysdate) - 10);
    END;