Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Posts
    5

    Unanswered: Problem in Creating a procedure to track history.

    Hi!

    Well here is the scenario.
    EMP TABLE
    empid
    name,
    datehired
    deptid

    DEPT TABLE
    deptid
    deptname
    etc

    To track history
    Dept_History
    historyid
    empid fk
    deptid
    tilldate

    to track history i've created following procedure.

    begin
    if OLD.deptid != NEW.deptid
    then
    insert into history
    (
    historyid,
    empid,
    deptid,
    tilldate
    )
    values
    (
    null,
    OLD.empid,
    OLD.deptid,
    now()
    );

    it works fine i am unable to determin the startdate, means on which date acutally an employee was assigned to a certain dept.

    to accomplish this i want to add on more column to the history table which is "fromdate" column.but i am unable to determin from where would i get date.

    Should i add "w.e.f" column to emp table next to dept id. or is there any other solution.

    I am thanking in advance as i am sure that someone out there is going to help.

    If need more clarification about the problem please post feek back.

    Thanks
    Hashmi

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I think you will need to provide more information. I think you are talking about triggers and not stored procedures? Is this correct? On which table is the trigger and for which operation INSERT, UPDATE or DELETE?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Aug 2011
    Posts
    5
    Yes you are right. I am sorry for creating confusion.

    Trigger is that which i want to create to update the history table automaticay.whenever any changes made in the employees table. So, it is going be an UPDATE operation in employees table and new isertion in History Table.

    Is it better to have a date " w.e.f " column in employees table?or through trigger it can be attained?

    if this column is there then user will have the option to change the date at any time.

    otherwise there may be a chance that if adminstrator did not apply the changes on the same date then histroy table will not represent the true record. It will only record that date on which changes were made.

    What do you suggest to do?

    Any other information you need please specify.

    Thank you for the the reply.
    Last edited by ahashmi; 09-30-11 at 07:55.

Posting Permissions

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