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

    Join Date
    Jun 2007
    Posts
    39
    Rep Power
    8

    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
    226
    Rep Power
    13
    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
    8
    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
    226
    Rep Power
    13
    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 10:10 AM.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

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

IMN logo majestic logo threadwatch logo seochat tools logo