SQL Record-plsql trigger

PL/SQL triggers

A trigger is a stored program that automatically executes or emits when some event occurs. Triggers, in fact, write responses to any of the following events will be executed:

• Database Operations (DML) statements (delete,insert,update or)

• Database definition (DDL) statements (create,alter or drop)

• Database Operations (Servererror, logon, logoff, startup or shutdown)

Triggers can be defined on a table, view, schema, or database associated with the event

Advantages OF Triggers

Triggers can be written for the following purposes:

• Automatically generate some derived column values

• Enforcing referential integrity

• Event logs and access to tables store information

• Audit

• Synchronous replication of tables

• Implement a security license

• Preventing illegal transactions

Create a Trigger

Syntax for creating triggers:

CREATE [ORREPLACE] TRIGGERTrigger_name{Before|After|INSTEADOf } {INSERT [OR] | UPDATE [OR] | DELETE} [of Col_name] on table_name [referencing old as  o NEW as N][for each row] when  (condition   declare Declaration-statementsbegin Executable-statementsexception exception-handling-statements end            

So

• create [or replace] TRIGGER trigger_name: Creates or replaces an existing trigger:trigger_name

• {before | After | INSTEAD of}: Specifies when the trigger is to be executed. The instead of clause is used to create a trigger in a view

• {INSERT [OR] | UPDATE [OR] | DELETE}: Specify DML operation

• [of Col_name]: Specifies the name of the column to be updated

• [ON table_name]: Specifies the name of the table associated with the trigger

• [Referencing old as O new as n]: various DML statements, such as insert,update and delete, that can refer to the new and previous values

• [For every row]: Specifies a row-level trigger that triggers each row to be affected. Otherwise, when executing the SQL statement, this is called a table-level trigger trigger that will be executed once

• When (condition): The condition that the trigger will trigger. This clause applies only to row-level triggers that are valid

Example:

First, we will use the Customers table that we have created and used in the previous chapters:

Select * FromCustomers;+----+----------+-----+-----------+----------+|Id|NAME|Age|ADDRESS|SALARY|+----+----------+-----+-----------+----------+| 1 |Ramesh| 32 |Ahmedabad| 2000.00 || 2 |Khilan| 25 |Delhi| 1500.00 || 3 |Kaushik| 23 |Kota| 2000.00 || 4 |Chaitali| 25 |Mumbai| 6500.00 || 5 || 27 || 8500.00 ||  6 || 22 ||4500.00 |+----+----------+-----+-----------+----------+       

The following program creates a row-level trigger in the Customers table that will trigger an INSERT or UPDATE or delete operation on the Customers table. The trigger displays the difference between the old and new values of the payroll:

CREATE ORREPLACETRIGGERDisplay_salary_changesbeforeDELETE OR INSERT OR UPDATE OnCustomersForeachROWWhen (NEW.Id> 0)DECLARESal_diff number;BEGINSal_diff:= :NEW.Salary- :Old.Salary;Dbms_output.Put_Line(' Old salary: ' | |  :old. Dbms_output. ( ' New Salary: '  | |  :new. Dbms_output. ( ' Salary difference: '  | |  Sal_diff); end;               

When the above code is executed at the SQL prompt, it produces the following results:

Trigger created.

Here the following two points are important and should be carefully noted:

• Old and new references are not available for table-level triggers, and they cannot be used for record-level triggers.

• If you want to query the same trigger in the table, then the app should use the After keyword, because the trigger can query the table, or change it again the original changes only apply after the table is back to the consistent state.

• The above trigger has been written in such a way that it will be executed before any delete, insert, or update operation on the table, but can take the trigger to write one or more operations, such as before Delete, which will trigger whenever a record will be deleted using the delete operation on the table.

Trigger a Trigger

Let's perform some DML operations on the Customers table. Here is an INSERT statement, which creates a new record in the table:

insert  CUSTOMERS  (id, Name,age,address,< Span class= "PLN" >salary) values (7,  ' Kriti ' , 22, , 7500.00     

When the record is created in the Customers table, the above trigger display_salary_changes will be triggered and it will show the following results:

Old salary:new salary:7500salary difference:

Because this is a new record, the base pay is not available and the result above is empty (null). Now, let's do one more DML operation on the Customers table. Here is an UPDATE statement that will update the existing records in the table:

UPDATE CustomersSET=+WHERE=2;        

When the record is updated in the Customers table, the above-created trigger display_salary_changes will be triggered, and it will show the following results:

Old Salary:1500new salary:2000salary difference:500

SQL record-plsql triggers