| Subject | Re: [firebird-support] How To Generate Auto Increment Number in SQL Itself ? |
|---|---|
| Author | Vishal Tiwari |
| Post date | 2015-03-16T05:37:49Z |
HI Louis,
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.
Vishal
On Saturday, 14 March 2015 5:01 PM, "'Louis van Alphen' louis@... [firebird-support]" <firebird-support@yahoogroups.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,
OTHER_COLUMN VARCHAR(64)
);
And trigger
/* Trigger: MY_TABLE_BI0 */
CREATE OR ALTER TRIGGER MY_TABLE_BI0 FOR MY_TABLE
ACTIVE BEFORE INSERT POSITION 0
AS
begin
NEW.TENANT_ID = (select rdb$get_context('USER_SESSION','TENANT_ID') from rdb$database);
end
^
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
select *
from MY_TABLE
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
(ID,OTHER_COLUMN)
values
(1,'Dome value')
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