473,231 Members | 1,439 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,231 developers and data experts.

TRIGGERS - 1

debasisdas
8,127 Expert 4TB
This thread contains some useful tips/sample codes regarding TRIGGERS in oracle, that the forum members may find useful.

TRIGGERS:
===============
Database trigger is a PL/SQL block that is executed on an event in the database.
The event is related to a particular data manipulation of a table such as inserting, deleting or updating a row of a table.

Triggers may be used :

1.To implement complex business rule, which cannot be implemented using integrity constraints.
2.To audit the process. For example, to keep track of changes made to a table.
3. To automatically perform an action when another concerned action takes place. For example, updating a table whenever there is an insertion of a row into another table.

Types of Triggers:
------------------------
Depending upon when a trigger is fired it may be classified as :

a.Statement-level trigger
b.Row-level trigger
c.Before triggers
d.After triggers
e.Instead of triggers

Syntax:
-----------
CREATE [OR REPLACE] TRIGGER triggername
{BEFORE | AFTER | INSTEAD OF}
{DELETE | INSERT | UPDATE [OF columns]}
[OR {DELETE | INSERT |UPDATE [OF columns]}]...
ON table_name|SCHEMA
[REFERENCING [OLD AS old] [NEW AS new]]
[FOR EACH ROW [WHEN condition]]
<PL/SQL block>

Trigger Sample Ex #1
====================

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE TRIGGER  MYTRIG AFTER INSERT OR DELETE ON EMP
  2. DECLARE
  3. MCOUNT NUMBER;
  4. BEGIN
  5. SELECT COUNT(*) INTO MCOUNT FROM EMP;
  6. --checks the number of records in the table.
  7. DBMS_OUTPUT.PUT_LINE('THE TOTAL NUMBER OF EMPLOYEE ARE:' || MCOUNT);
  8. --and displays the same after the event .
  9. END;
  10.  
  11.  
Trigger Sample Ex #2
================

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE TRIGGER ONLYPOSITIVE BEFORE INSERT OR UPDATE OF SAL ON EMP FOR EACH ROW
  2. BEGIN
  3. IF :NEW.SAL<0 THEN
  4. --checks of the value inserted into teh sal column is less than 0.
  5. RAISE_APPLICATION_ERROR(-20100,'PLEASE INSERT A POSITIVE VALUE');
  6. --if valus is less than 0 ,then raise the exception.
  7. END IF;
  8. END;
Note:-Trigger does not allow negative values to insert/update to the table.

trigger example #3
==================

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE TRIGGER NODELETE BEFORE INSERT OR UPDATE OR DELETE ON DEPT FOR EACH ROW
  2. BEGIN
  3. IF INSERTING THEN
  4. RAISE_APPLICATION_ERROR(-20020,'CAN''T INSERT NEW RECORDS TO THIS TABLE');
  5. ELSIF UPDATING THEN
  6. RAISE_APPLICATION_ERROR(-20021,'CAN''T UPDATE RECORDS OF THIS TABLE');
  7. ELSIF DELETING THEN
  8. RAISE_APPLICATION_ERROR(-20022,'YOU ARE NOT AUTHORISED TO DELETE THESE RECORDS');
  9. END IF;
  10. END;
Note:- the triggermakes the trigger readonly by not allowing any DML on it.

Trigger Sample Ex #4
================
Expand|Select|Wrap|Line Numbers
  1. create or replace trigger mytrig
  2. before insert on emp
  3. declare
  4. a char(3);
  5. begin
  6. --selects the system day to the vaiable.
  7. select to_char(sysdate,'dy') into a from dual;
  8. if a in('sun','sat') then
  9. --restrict any transaction on saturday and sunday.
  10. Raise_application_error(-20004,'Cannot do manipulation today');
  11. end if;
  12. end;
Note:--This trigger restricts transactions on the table on weekends.


Also check TRIGGERS - 2
Jun 13 '07 #1
0 4454

Sign in to post your reply or Sign up for a free account.

Similar topics

11
by: raulgz | last post by:
I need audit triggers that change columns value in the same record that fire trigger. I need how to do.. Thanks..
1
by: jason_s_ford | last post by:
I have several sql server databases that were recently moved to a new server. In the process of migrating the databases, any triggers and constraints attached to tables were removed on accident. ...
4
by: Mark Flippin | last post by:
I'm just starting to use triggers in my databases and find the support in Enterpise Manager lacking. Using Enterprise Manager and Query Analyzer you can maintain the triggers, but it's...
1
by: tim.pascoe | last post by:
I'm trying to generate scrips for a database, and everything so far has worked fine, except for the triggers. When I try and script existing triggers, all I get is a blank file - no SQL script. I...
4
by: stacdab | last post by:
We have a partitioned view with 4 underlying tables. The view and each of the underlying tables are in seperate databases on the same server. Inserts and deletes on the view work fine. We then...
5
by: Bruce | last post by:
I have several user defined functions which are referenced in triggers and views. For software upgrades, I need to be able to drop the triggers and views which reference these user defined...
0
by: Bruno Lavoie | last post by:
Hello, i'm etablishing a naming convention for a new project under postgresql. For tables, sequences, views, that's ok! I used good naming conventions for this in the past and i'll keep these...
0
debasisdas
by: debasisdas | last post by:
trigger sample code Ex#10 ======================= INSTEAD OF TRIGGER ---------------------------------------- create or replace trigger mytrig instead of delete or insert or update on eview...
4
by: --CELKO-- | last post by:
I need to convert a bunch of DB2 triggers to Oracle. Is there any kind of tools for this?
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.