Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    Posts
    62

    Unanswered: Can i do this in a trigger?

    Greetings DBAs
    The scenario:
    I have 2 tables

    TableLive
    STAFFID,THE_LATEST_HE_DID,REGISTRATIONDATE,CREATED

    TableAudit
    STAFFID,THE_LATEST_HE_DID,REGISTRATIONDATE,CREATED

    Both table are identical in structures. I have an application that will do insert only to table TableLive, right now I don't have any conditions in the application.
    I want to a have a trigger, where :

    1. during INSERT into TableLive, if the REGISTRATIONDATE > :NEW.REGISTRATION don't insert into TableLive, but insert into TableAudit
    2. during INSERT into TableLive, if the REGISTRATIONDATE < :NEW.REGISTRATION insert into TableLive but delete and insert old record into TableAudit

    The objective is to have only unique STAFFID and the latest what he did, the latest is based on registration date, this registration is free datetime

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Your design is flawed. Your should have a parent table that contains all the staff information, but no regestration information and a child table that contains all the registration information for the staff. Then a simple quest where you find the highest registrationdate will find the last row.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •