PostgreSQL Triggers can be efficiently worked upon either via PgAdmin or via psql terminal as shown below: Access Trigger(s) via PgAdmin: CREATE TRIGGER stocks_triggerĮXECUTE PROCEDURE stock_auditfunc() PostgreSQL Trigger Access Through PgAdmin and PSQL Terminal This would execute the trigger just once per operation irrespective of the number of rows affected. STATEMENT TRIGGER: The above trigger definition can be modified in the following way. Note: For bulk operation for example where 100 rows are affected at once, the row trigger is executed 100 times. Table after insert and trigger execution: INSERT INTO public."Stocks" INSERT INTO stocks_audit(stock_id, entry_date) VALUES (new.ID, current_timestamp) CREATE TRIGGER stocks_triggerĮXECUTE PROCEDURE stock_auditfunc() CREATE OR REPLACE FUNCTION stock_auditfunc() RETURNS TRIGGER AS $my_table$ Scenario: There are two tables stocks & stock_audits, for every row of data inserted in table stocks the trigger stocks_trigger is executed thus inserting one row of data in the other table stocks_audit. Once a trigger function is defined it can be associated to one or more trigger events such as insert, update and delete. PostgreSQL Trigger Function Basic Syntax: CREATE FUNCTION trigger_function()Ī trigger function does not take any arguments and has a return value with the type trigger. Finally, the associated trigger_function is specified. Next the table_name is specified, followed by the type of the trigger which is either row or statement. This is followed by the event which is one of the following - insert, update, delete, truncate. This is followed by the timing of the trigger which can be either before or after depending on the operation to be performed on the target table. To start trigger_name is specified to create the trigger. PostgreSQL Trigger Basic Syntax: CREATE TRIGGER trigger_name Enable trigger – Is used to enable a specific or all triggers associated with table.Disable trigger– Is used to disable a specific or all triggers associated with table.Alter trigger - Is used to change the name of an existing trigger.Drop trigger - Is used to drop a trigger.Create trigger - Is used to create a trigger.PostgreSQL Trigger supports the following DML operations: PostgreSQL doesn't support triggers without a related trigger function.PostgreSQL triggers supports truncate operations.PostgreSQL TriggersĬonceptually a PostgreSQL trigger is similar to that of a SQL Server trigger, but there are some key differences, which are listed below: Taking the above example, if a statement level insert trigger is defined on a table where a single insert statement inserts 100 rows in the table the trigger is executed one time. In other words whenever some operation is performed on a table irrespective of the number of rows being worked upon, the trigger is fired only once. Statement level triggerĪs the name implies a statement level trigger is executed only once per statement or per transaction. For example, if a row level insert trigger is defined on a table where a single insert statement inserts 100 rows in the table the trigger is executed 100 times once for each row. Row level triggerĪ row level trigger is triggered every time a row in a table gets affected. There are mainly two types of triggers: row and statement level triggers. Another disadvantage of triggers is they are hard to track and understanding their logic can be difficult.Since triggers are executed every time there is a modification of data, this can lead to system overhead. While triggers are really useful in automating data alterations and allowing easy auditing of data, there are some disadvantages of triggers, too.Triggers are very useful when a database is being used by multiple applications, and there is a great need to keep the database in sync at all times whenever certain data is modified.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |