Reset Generator Table using Trigger

Dev Shed Forums Sponsor:

January 15th, 2012, 09:29 AM

rusdyrip

Contributing User

Join Date: Jun 2007

Posts: 38

Time spent in forums: 11 h 55 m 56 sec
Reputation Power: 6

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

January 16th, 2012, 02:05 AM

mIRCata

Contributing User

Join Date: Sep 2006

Location: Plovdiv. Bulgaria

Posts: 200

Time spent in forums: 2 Days 6 h 50 m 4 sec
Reputation Power: 11

Quote:

Originally Posted by rusdyrip

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';

January 16th, 2012, 07:21 AM

rusdyrip

Contributing User

Join Date: Jun 2007

Posts: 38

Time spent in forums: 11 h 55 m 56 sec
Reputation Power: 6

Quote:

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

January 16th, 2012, 09:00 AM

mIRCata

Contributing User

Join Date: Sep 2006

Location: Plovdiv. Bulgaria

Posts: 200

Time spent in forums: 2 Days 6 h 50 m 4 sec
Reputation Power: 11

Quote:

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.

January 16th, 2012, 09:00 PM

rusdyrip

Contributing User

Join Date: Jun 2007

Posts: 38

Time spent in forums: 11 h 55 m 56 sec
Reputation Power: 6

thank you,your solution is the best for my case

Reset Generator Table using Trigger

Developer Shed Advertisers and Affiliates