Help - Timestamp on Update?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Help - Timestamp on Update?

Hybrid View

  1. #1
    Join Date
    May 2001
    Location
    Sydney Australia
    Posts
    44

    Unhappy

    G'day! I'm trying to update a "timestamp" sort of column in a table, whenever it is updated. It is entered as a default value on insert. The column's name is "UpdatedDT".

    I must have done something really stupid here... I cannot get the following to compile... can anyone suggest my mistake please?

    CREATE TRIGGER "ISPUSER"."UPDATECHILD"
    BEFORE UPDATE
    ON "ISPUSER"."CHILD"
    FOR EACH ROW
    BEGIN
    SELECT SYSDATE
    INTO :new.UpdatedDT
    FROM dual;
    END

    Many thanks in advance
    Max Hugen
    (non-OCP, hard-working, jack-of-all-trades Internet application developer )
    Max Hugen
    Hugen Enterprises Pty Ltd
    www.hugen.com.au

  2. #2
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    CREATE OR REPLACE TRIGGER "SCOTT"."TEST"
    BEFORE UPDATE ON "SCOTT"."TASK_LOG"
    FOR EACH ROW
    BEGIN
    SELECT SYSDATE
    INTO :NEW.start_date
    FROM dual;
    END;

    /


    This compiled, I just put a ; after END.

    Cheers
    OCP 8i, 9i DBA
    Brisbane Australia

  3. #3
    Join Date
    Aug 2001
    Posts
    5
    Hi,
    The earliest timestamp and latest timestamp methods are variations on the minimum and maximum value methods. To use the timestamp method, you must designate a column in the replicated table of type DATE. When an application updates any column in a column group, the application must also update the value of the designated timestamp column with the local SYSDATE. For a change applied from another site, the timestamp value should be set to the timestamp value from the originating site.

    There are several elements needed to make timestamp conflict resolution work well:

    Synchronized Time Settings Between Computers

    Timestamp field and trigger to automatically record timestamp

    --The following procedures need to be executed by the replication administrator.

    CONNECT repadmin/repadmin@orc1.world

    --Before you can define any conflict resolution routines, you need to quiesce
    --the master group that contains the table that you want to apply the conflict
    --resolution routine to.

    BEGIN
    DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
    gname => 'SCOTT_MG');
    END;
    /

    --If the target table does not already contain a timestamp field,
    --then you need to add an additional column to your table to record
    --the timestamp value when a row is inserted or updated. Additionally,
    --you must use the ALTER_MASTER_REPOBJECT API to apply the DDL to
    --the target table (simply issuing the DDL may cause the replicated
    --object to become invalid).

    BEGIN
    DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
    sname => 'SCOTT',
    oname => 'EMP',
    type => 'TABLE',
    ddl_text => 'ALTER TABLE scott.emp ADD (timestamp DATE)');
    END;
    /

    --After you have inserted a new column into your replicated object,
    --you need to make sure that you re-generate replication support for
    --the affected object. This step should be performed immmediately
    --after you alter the replicated object.

    BEGIN
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
    sname => 'SCOTT',
    oname => 'EMP',
    type => 'TABLE',
    min_communication => TRUE);
    END;
    /

    --Once the timestamp field has been created, you need to create a
    --trigger that records the timestamp of when a row is either inserted
    --or updated. This recorded value will be used in the resolution of
    --conflicts based on the Timestamp method. Instead of directly executing the
    --DDL, you should use the DBMS_REPCAT.CREATE_MASTER_REPOBJECT procedure to
    --create the trigger and add it to your master group.

    BEGIN
    DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
    gname => 'SCOTT_MG',
    type => 'TRIGGER',
    oname => 'INSERT_TIME',
    sname => 'SCOTT',
    ddl_text => 'CREATE TRIGGER scott.insert_time
    BEFORE
    INSERT OR UPDATE ON scott.emp FOR EACH ROW
    BEGIN
    IF DBMS_REPUTIL.FROM_REMOTE = FALSE THEN
    :NEW.TIMESTAMP := SYSDATE;
    END IF;
    END;');
    END;
    /

    BEGIN
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
    sname => 'SCOTT',
    oname => 'INSERT_TIME',
    type => 'TRIGGER',
    min_communication => TRUE);
    END;
    /

    --All Oracle conflict resolution routines are based on logical column groupings
    --termed "column groups." Create a column group for your target table by using
    --the DBMS_REPCAT.MAKE_COLUMN_GROUP procedure.

    BEGIN
    DBMS_REPCAT.MAKE_COLUMN_GROUP (
    sname => 'SCOTT',
    oname => 'EMP',
    column_group => 'EMP_CG1',
    list_of_column_names => 'MGR, HIREDATE, SAL, TIMESTAMP');
    END;
    /

    --Use the DBMS_REPCAT.ADD_UPDATE_RESOLUTION API to define the conflict
    --resolution routine for a specified table. This example will specify the
    --"LATEST TIMESTAMP" conflict resolution routine using the TIMESTAMP column
    --that you created earlier.

    BEGIN
    DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
    sname => 'SCOTT',
    oname => 'EMP',
    column_group => 'EMP_CG1',
    sequence_no => 1,
    method => 'LATEST TIMESTAMP',
    parameter_column_name => 'TIMESTAMP');
    END;
    /

    --After you have defined your conflict resolution routine, you need to
    --regenerate replication support for the table that received the conflict
    --resolution routine.

    BEGIN
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
    sname => 'SCOTT',
    oname => 'EMP',
    type => 'TABLE',
    min_communication => TRUE);
    END;
    /

    --After replication support has been regenerated, you need to resume replication
    --activity by using the RESUME_MASTER_ACTIVITY procedure API.

    BEGIN
    DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
    gname => 'SCOTT_MG');
    END;
    /


    Hope this helps you. Let me know if you further questions...

    Regards,
    Senthil Babu J
    SlashSupport.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by maxhugen
    CREATE TRIGGER "ISPUSER"."UPDATECHILD"
    BEFORE UPDATE
    ON "ISPUSER"."CHILD"
    FOR EACH ROW
    BEGIN
    SELECT SYSDATE
    INTO :new.UpdatedDT
    FROM dual;
    END
    You don't actually need to select SYSDATE from DUAL in PL/SQL. You can simply assign it to a variable:

    CREATE TRIGGER "ISPUSER"."UPDATECHILD"
    BEFORE UPDATE
    ON "ISPUSER"."CHILD"
    FOR EACH ROW
    BEGIN
    :new.UpdatedDT := SYSDATE;
    END;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    May 2001
    Location
    Sydney Australia
    Posts
    44

    Thumbs up

    Many thanks, it was indeed the missing semi-colon after "end".

    Cheers!
    Max Hugen
    Hugen Enterprises Pty Ltd
    www.hugen.com.au

Posting Permissions

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



Click Here to Expand Forum to Full Width