|Subject||Re: [firebird-support] How To Generate Auto Increment Number in SQL Itself ?|
It's pretty cool, but i need only in SQL, that too same number for all records for same employee and next incremental number for all records of the another employee.
SET any help offered ?
Thanks In Advance.
With Best Regards.
On Saturday, 14 March 2015 5:01 PM, "'Louis van Alphen' louis@... [firebird-support]" <email@example.com> wrote:
Of course, using get_context and set_context is a great way to implement true multi-tenancy at DB level in Firebird. Multi-tenancy can be dangerous if implemented in middleware or SQLs because programmers make mistakes and can forget a where clause. What we want to do is to have the DB abstract that for us.
Create the following table:
CREATE TABLE MY_TABLE (
ID BIGINT NOT NULL,
TENANT_ID BIGINT NOT NULL,
/* Trigger: MY_TABLE_BI0 */
CREATE OR ALTER TRIGGER MY_TABLE_BI0 FOR MY_TABLE
ACTIVE BEFORE INSERT POSITION 0
NEW.TENANT_ID = (select rdb$get_context('USER_SESSION','TENANT_ID') from rdb$database);
SET TERM ; ^
We see that the trigger populates the TENANT_ID for us from a context variable that is valid for the whole session.
Now what we want is to have our view only return rows belonging to the logged-in tenant. I.e.:
create view VW_MY_TABLE as
where TENANT_ID = (select rdb$get_context('USER_SESSION','TENANT_ID') from rdb$database);
So all you need to do at the beginning of the session is to set the context:
select RDB$SET_CONTEXT('USER_SESSION','TENANT_ID',1) from rdb$database
and insert data
insert into MY_TABLE
Select * from VW_MY_TABLE will return our row
Change to a different tenant
select RDB$SET_CONTEXT('USER_SESSION','TENANT_ID',2) from rdb$database
Select * from VW_MY_TABLE will now return empty
Pretty cool… You just jave to watch your indexing