Java7
Posts tagged trigger
Creating Oracle “AUTO_INCREMENT” column for your surrogate key
Mar 9th
Lets face it, nobody tries to create primary keys out from natural keys in today’s context, maybe due to the sheer number of database tables an application or system today requires.
Creating surrogate keys is simple as it is common to most people. Yet Oracle has made this task a non trivial one.
I have come upon the chance to create a new table supporting new business requirements. The guide steps through using Oracle SQL Developer.
Step 1: Create Table
Create a new table of your choice.
** The steps to create a table are omitted.
For the purpose of this guide, we are interested to make column STATUS_ID of table GL_RECORD_STATUS_MATRIX an auto incremented surrogate key. STATUS_ID is also the primary key of the table.
Step 2: Create Sequence
Auto incremental numbers come in the form of sequence in Oracle. It is necessary to create a sequence object.
Name your sequence. For my sequence, i have chosen “SEQ_GL_RECORD_STATUS_MATRIX” in the naming convention “SEQ” + <table name>.
Create your sequence with the following properties:
Increment 1
Min value 0
Max value 999999999
Start with 1
Step 3: Create Trigger
In the configuration dialog, name your trigger and select the table you wish to apply this trigger to. You may choose to name your sequence trigger in the convention of <table name> + “SEQ” or “SEQ” + <table name> depending on the naming scheme you prefer.
Select the “Row Level” option as we are applying this trigger on a row level.
Once you have created your trigger, SQL Developer should display the “code” of the trigger that you have just created.
CREATE OR REPLACE TRIGGER GL_RECORD_STATUS_MATRIX_SEQ
BEFORE INSERT ON GL_RECORD_STATUS_MATRIX
FOR EACH ROW
BEGIN
NULL;
END;
Note that between the BEGIN and END keywords is a NULL literal. This is where you fill in the “how” of this trigger.
CREATE OR REPLACE TRIGGER GL_RECORD_STATUS_MATRIX_SEQ
BEFORE INSERT ON GL_RECORD_STATUS_MATRIX
FOR EACH ROW
BEGIN
select SEQ_GL_RECORD_STATUS_MATRIX.nextval into :NEW.STATUS_ID from dual;
END;
Note that i have replaced the string literal “NULL” with the following:
select SEQ_GL_RECORD_STATUS_MATRIX.nextval into :NEW.STATUS_ID from dual;
This statement means, select the next value from the sequence i have just created, and insert into onto STATUS_ID of my table.
Compile the trigger when done.
Complete! Testing…
Test that your auto number is working by adding a new row to your table without explicitly providing a value to the STATUS_ID field. Oracle should insert the value of STATUS_ID automatically for you.
Recap…
Usually, it is not necessary to add a prefix to the name of the object that you are creating, for example, it is not necessary to name an employee table tbl_employee or table_employee. However, because Oracle does not quite allow us to create a sequence object with the same name as the table object in the same schema, we have no choice but to select another more definitive name for our sequence.
(Unable to create a sequence with the same name as the table)
PostgreSQL Show Triggers and Map to Tables
Dec 27th
I came across the need to list all triggers in PostgreSQL, it returns painful and verbose results. Something like this.
It doesn’t show you the table the trigger belongs to and you would need to map through an oid.
ebarges=> select * from pg_trigger;
tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisconstraint | tgconstrname | tgconstrrelid | tgconstraint | tgdeferrable |
tginitdeferred | tgnargs | tgattr | tgargs
---------+-----------------------------------------+--------+--------+-----------+----------------+--------------+---------------+--------------+--------------+
----------------+---------+--------+--------
1262 | pg_sync_pg_database | 1689 | 28 | O | f | | 0 | 0 | f |
f | 0 | |
1260 | pg_sync_pg_authid | 1689 | 28 | O | f | | 0 | 0 | f |
f | 0 | |
1261 | pg_sync_pg_auth_members | 1689 | 28 | O | f | | 0 | 0 | f |
f | 0 | |
50288 | map_iso_uom_trigger | 50126 | 31 | O | f | | 0 | 0 | f |
f | 0 | |
50293 | map_location_trigger | 50127 | 31 | O | f | | 0 | 0 | f |
f | 0 | |
50298 | map_product_trigger | 50128 | 31 | O | f | | 0 | 0 | f |
f | 0 | |
50303 | map_storage_location_route_code_trigger | 50129 | 31 | O | f | | 0 | 0 | f |
f | 0 | |
50326 | master_route_code_trigger | 50130 | 31 | O | f | | 0 | 0 | f |
f | 0 | |
50331 | master_storage_location_trigger | 50131 | 31 | O | f | | 0 | 0 | f |
f | 0 | |
50358 | user_info_trigger | 50132 | 31 | O | f | | 0 | 0 | f |
f | 0 | |
(10 rows)
In order to narrow down the search to show only columns i need, i issued the describe table command. This official reference shows the PostgreSQL pg_trigger table description otherwise.
ebarges=> \d pg_trigger
Table "pg_catalog.pg_trigger"
Column | Type | Modifiers
----------------+------------+-----------
tgrelid | oid | not null
tgname | name | not null
tgfoid | oid | not null
tgtype | smallint | not null
tgenabled | "char" | not null
tgisconstraint | boolean | not null
tgconstrname | name | not null
tgconstrrelid | oid | not null
tgconstraint | oid | not null
tgdeferrable | boolean | not null
tginitdeferred | boolean | not null
tgnargs | smallint | not null
tgattr | int2vector | not null
tgargs | bytea |
Indexes:
"pg_trigger_oid_index" UNIQUE, btree (oid)
"pg_trigger_tgrelid_tgname_index" UNIQUE, btree (tgrelid, tgname)
"pg_trigger_tgconstrname_index" btree (tgconstrname)
I went on to show only the columns i needed, which are tgrelid and tgname.
ebarges=> select tgrelid, tgname from pg_trigger;
tgrelid | tgname
---------+-----------------------------------------
1262 | pg_sync_pg_database
1260 | pg_sync_pg_authid
1261 | pg_sync_pg_auth_members
50288 | map_iso_uom_trigger
50293 | map_location_trigger
50298 | map_product_trigger
50303 | map_storage_location_route_code_trigger
50326 | master_route_code_trigger
50331 | master_storage_location_trigger
50358 | user_info_trigger
(10 rows)
tgrelid appears to be a table oid of the table the trigger belongs to, it works equally the same as the foreign key concept. Now to find out the mapping between the oid and its table, one of the ways is to use the oid2name utility program. oid2name reference documentation
From your shell, call oid2name the same way as you would for psql when logging in to the PostgreSQL server. You may be prompted for a password if it exists for your account.
Admin@Admin-THINK ~
$ oid2name -U ebarges_user -d ebarges
You will see these results similar to your schema.
Admin@Admin-THINK ~
$ oid2name -U ebarges_user -d ebarges
Password:
From database "ebarges":
Filenode Table Name
-------------------------------------------------
50288 map_iso_uom
50293 map_location
50298 map_product
50303 map_storage_location_route_code
50326 master_route_code
50331 master_storage_location
From this point you should be able to map the oid from the trigger to the appropriate table. Cheers.
* Do note that oid may be (it most certainly will be) different from server to server, i.e. your PostgreSQL database in production server from your PostgreSQL database in staging server regardless of version.
