[sqlalchemy] Creating Oracle Triggers on Table Create

[Gerrat]

We have an oracle database, and all of our tables have auto-generating
id's on the primary key via a trigger that looks like :
-
CREATE OR REPLACE TRIGGER SCHEMA.some_table_rid
BEFORE INSERT OR UPDATE OF rid ON some_table
FOR EACH ROW
BEGIN
  IF UPDATING
  THEN
:new.rid := :old.rid;
  END IF;
  IF INSERTING
  THEN
IF :new.rid = 0
  THEN
  SELECT some_table_rid.NEXTVAL INTO :new.rid FROM dual;
ELSE
  DECLARE
num1 number(12);
  BEGIN
SELECT last_number INTO num1 FROM USER_SEQUENCES WHERE
SEQUENCE_NAME = 'SOME_TABLE_RID';
IF num1 = :new.rid
THEN
  LOOP
SELECT some_table_rid.NEXTVAL INTO num1 FROM dual;
EXIT WHEN num1 = :new.rid;
  END LOOP;
END IF;
  END;
END IF;
  END IF;
END;
-
This allows any external tool (like SQL Alchemy) to control primary
key generation via the sequence, but still allows the next id to be
inserted automatically by just inserting a 0 into the table for the
primary key

I'd like a similiar trigger created on every table create that
included a Sequence as part of a primary_key col.

Is there a way to easily hook into the table creation mechanism so
that whenever a Sequence was specified as part of a Column definition,
a similiar trigger could be inserted (substituting some names
obviously)?  I know I can manually issue the DDL after-the-fact for
each table create, but would prefer it be more implicit.

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Creating Oracle Triggers on Table Create

[Gerrat]

On Aug 19, 2:11 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 On Aug 19, 2008, at 1:59 PM, Gerrat wrote:



  Is there a way to easily hook into the table creation mechanism so
  that whenever a Sequence was specified as part of a Column definition,
  a similiar trigger could be inserted (substituting some names
  obviously)?  I know I can manually issue the DDL after-the-fact for
  each table create, but would prefer it be more implicit.

 I'm assuming that you mean you're already familiar with the DDL()  
 construct, which is documented at:

 http://www.sqlalchemy.org/docs/05/sqlalchemy_schema.html#docstrings_s...

 So the next step is to create your own Table function along the lines  
 of:

 from sqlalchemy.schema import Table as _Table, Sequence, DDL

 def Table(name, meta, *args, **kw):
         t = _Table(name, meta, *args, **kw)
         for c in t.c:
                 if c.default and isinstance(c.default, Sequence):
                         DDL(your per-sequence DDL 
 here).execute_at('after-create', t)
         return t


Thanks for the super-quick response, Michael.
That's exactly what I was looking for!

...not sure where my head was at...didn't think of just browsing the
sources to look at the Table class.  Pretty straight forward - thanks
again!
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---