current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

I have a transaction table, which has tenant_id and transaction_id columns (they make up a unique composite index). For insert operation, transaction_id must be incremented by 1, but for given tenant_id. So, using sqlalchemy framework, I manually find max transaction_id for tenant_id:

res = db.session.query(func.max(my_tran.transaction_id).label('last_id')) \
                                     .filter_by(tenant_id=tenant_id).one()
if res.last_id:
    my_tran.transaction_id = res.last_id
else:
    my_tran.transaction_id = 1

What I'd like to do instead is define the logic for my model class as server default:

class MyTran(db.Model):
    __tablename__ = 'my_tran'
    id = db.Column(db.Integer, primary_key=True)
    tenant_id = db.Column(db.Integer, db.ForeignKey('tenant.id'), nullable=False)
    transaction_id = db.Column(db.Integer, nullable=False, \
                              server_default='compute last id for tenant_id + 1')

I guess I need to create a trigger (how?), but don't know how to link to my model class.

share|improve this question

1 Answer 1

up vote 2 down vote

I found my answer here. Details are below (using sqlalchemy):

create_fn_my_tran_set_num = DDL(
'''
CREATE OR REPLACE FUNCTION fn_my_tran_set_num() 
RETURNS TRIGGER AS $$ 
DECLARE last_transaction_id INTEGER; 
BEGIN 
    last_transaction_id := MAX(transaction_id) FROM my_tran WHERE tenant_id = NEW.tenant_id; 
    IF last_transaction_id IS NULL THEN 
        NEW.transaction_id := 1; 
    ELSE 
        NEW.transaction_id := last_transaction_id + 1; 
    END IF; 
    RETURN NEW; 
END$$ 
LANGUAGE plpgsql 
''')
event.listen(MyTran.__table__, 'after_create', create_fn_my_tran_set_trx_id)
create_tg_my_tran_set_num = DDL(
'''
CREATE TRIGGER tg_my_tran_set_num 
BEFORE INSERT ON my_tran 
FOR EACH ROW 
EXECUTE PROCEDURE fn_my_tran_set_num(); 
''')
event.listen(MyTran.__table__, 'after_create', create_tg_my_tran_set_trx_id)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.