DDL in Trigger ?

[Lemox]

Hello,

I got a special sequence to write (it must use system date + ...).
In order to do that, I got to modify my sequence (re-set the minvalue,...).

Is it possible to write such an 'alter sequence' command within a trigger ? (I'm getting an error in DBA Studio but not very explicit)

Thanks,
Lemox

 

[JarlH]

Its not ANSI compliant anyway. Neither DDL in triggers nor alter sequence are.

 

[JarlH]

Oops, DDL statements in triggers are "implementation defined", i.e. the SQL standard doesn't define if its allowed or not.

 

[Lemox]

Well...

Let's forget DDL in triggers : how could I implement a sequence for a key that is made of 'YEAR' =4 first digits + and a 'real sequence' stuck at the end ?

i.e : 2003000001, 2003000002,..., 2004000001, 2004000002

I guess I got to use the system date but ???

 

[r937]

use a view with year(current_date) concatenated to the sequence column

resetting the sequence at the start of a new year ought to be fun...


rudy

http://r937.com/

 

[nigelrivett]

I take it that you want to have an id for each record yyyynnnnnnnn which depends on the order of insert.

It is easier to hold these in separate fields.
insyear, insseq

Then have another table to hold the current seqno

insyear, lastseqno

the trigger then adds one to the lastseqno for the insert. If insyear is not the same as the current year then it is updated and lastseqno set to 1.

Another option is to hold the inseq as a sequence number and the seqno table holds the first seqno for each year. This is attractive as the seqno can be used for referential integrity and the year just becomes an attribute - or coulld be obtained from the seqno table. The problem is that the first seqno for each year has to be added to the seqno table at year end. You could do this by a trigger but it would be wastefull - better to run a process once a year to set it if possible.

In both cases the data has to be concatenated for presentation - this could be done in a view or in your database access layer or whatever. It is a good idea not to bind presentation to the database structure.



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.