#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2007
    Posts
    39
    Rep Power
    10

    Reset Generator Table using Trigger


    hi all,
    i want to ask how do i reset generator to 0 after i check
    there is no record on table

    i want use it on trigger,so after user delete all row on table
    the generator is set to 0,
    i just now syntax to reset generator to 0 but don't know how to
    use it on trigger.

    syntax :
    ALTER SEQUENCE GEN_MASTERBARANG_ID RESTART WITH 0;

    thx
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    236
    Rep Power
    16
    Originally Posted by rusdyrip
    hi all,
    i want to ask how do i reset generator to 0 after i check
    there is no record on table

    i want use it on trigger,so after user delete all row on table
    the generator is set to 0,
    i just now syntax to reset generator to 0 but don't know how to
    use it on trigger.

    syntax :
    ALTER SEQUENCE GEN_MASTERBARANG_ID RESTART WITH 0;

    thx
    The only wat to do this in stored procedure or trigger is with EXECUTE STATEMENT
    Code:
    EXECUTE STATEMENT 'ALTER SEQUENCE GEN_MASTERBARANG_ID RESTART WITH 0';
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2007
    Posts
    39
    Rep Power
    10
    Originally Posted by mIRCata
    The only wat to do this in stored procedure or trigger is with EXECUTE STATEMENT
    Code:
    EXECUTE STATEMENT 'ALTER SEQUENCE GEN_MASTERBARANG_ID RESTART WITH 0';
    thank you for the function but how i detect if the row already
    zero?

    i don't really powerful on stored procedure query,pls tell me
    the solution

    thx
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    236
    Rep Power
    16
    Originally Posted by rusdyrip
    thank you for the function but how i detect if the row already
    zero?

    i don't really powerful on stored procedure query,pls tell me
    the solution

    thx
    Code:
    CREATE OR ALTER TRIGGER TR_TABLE_NAME_AD1101 FOR TABLE_NAME
    ACTIVE AFTER DELETE POSITION 1101
    AS
    DECLARE VARIABLE L_COUNT INTEGER;
    BEGIN
        SELECT 
            COUNT(*)
        FROM
            TABLE_NAME
        INTO
            :L_COUNTER;
        IF (COALESCE(L_COUNTER,0) = 0) THEN    
            EXECUTE STATEMENT 'ALTER SEQUENCE SEQUENCE_NAME RESTART WITH 0';
    END;
    But counting all records in a table after each delete is not a good idea. Especially for tables having large number of records.

    Another way is to use SELECT FIRST 1. You have to choose one field that always have a value (not null, maybe the field that is the primary key in the table) from the table:
    Code:
    CREATE OR ALTER TRIGGER TR_TABLE_NAME_AD1101 FOR TABLE_NAME
    ACTIVE AFTER DELETE POSITION 1101
    AS
    DECLARE VARIABLE L_VARIABLE SAME_TYPE_AS_THE_FIELD'S_TYPE;
    BEGIN
        SELECT FIRST 1
            FIELD_NAME
        FROM
            TABLE_NAME
        INTO
            :L_VARIABLE;
    
        IF (L_VARIABLE IS NULL) THEN    
            EXECUTE STATEMENT 'ALTER SEQUENCE SEQUENCE_NAME RESTART WITH 0';
    END;
    Last edited by mIRCata; January 16th, 2012 at 09:10 AM.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2007
    Posts
    39
    Rep Power
    10
    thank you,your solution is the best for my case

IMN logo majestic logo threadwatch logo seochat tools logo