472,779 Members | 1,960 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 472,779 developers and data experts.


8,127 Expert 4TB
trigger sample example #5

Expand|Select|Wrap|Line Numbers
  1. create or replace trigger mytrig
  2. before UPDATE OF SAL on emp for each row
  3. when(new.deptno=30)
  4. begin
  5. if :new.sal<:OLD.SAL then
  6. raise_application_error(-20003,'Sal should be > THE OLDER SAL');
  7. end if;
  8. end;
trigger sample example #6
Expand|Select|Wrap|Line Numbers
  1. create or replace trigger mytrig
  2. before insert on emp
  3. for each row
  4. begin
  5. select myseq.nextval | max(empno)+1 into :new.empno from emp;
  6. end;
trigger sapmle example #7
The table responsible for backup:
Expand|Select|Wrap|Line Numbers
  1. create table back 
  2. (
  3. empno number(4),
  4. ename varchar2(10), 
  5. sal number(7,2), 
  6. deptno number(2),
  7. uname varchar2(10), 
  8. cdate date,
  9. operation varchar2(15)
  10. );
trigger code
Expand|Select|Wrap|Line Numbers
  1. create or replace trigger mytrig
  2. before insert or update or delete on emp 
  3. for each row
  4. begin
  5. if inserting then
  6. insert into back values(:new.empno,:new.ename,:new.sal,:new.deptno,user,sysdate,'INSERTION');
  7. elsif updating then
  8. insert into back values(:old.empno,:old.ename,:old.sal,:old.deptno,user,sysdate,'UPDATION');
  9. else
  10. insert into back values(:old.empno,:old.ename,:old.sal,:old.deptno,user,sysdate,'DELETION');
  11. end if;
  12. end;
Note:--Monitoring the user transactions on the table in another table with out the knowledge of the user.

trigger sample code #8
Write this trigger in system/sys user.
Expand|Select|Wrap|Line Numbers
  1. create or replace trigger mytrig1
  2. after logon on scott.schema
  3. declare
  4. a char(3);
  5. begin
  6. select to_char(sysdate,'dy') into a from dual;
  7. if a in('sun','sat') then
  8. Raise_application_error(-20004,'Cannot do LOGIN today');
  9. end if;
  10. end;
Note :--This schema level trigger restricts the user(in this example--SCOTT) from logging in into the database on weekend.

trigger sample ex #9
Expand|Select|Wrap|Line Numbers
  3. BEGIN
  6. END IF;
  7. END;
Note :--the trigger restricts droping of any object whose owner is DEBASIS
Since triggers can't validate themselves.
The above trigger can't restrict droping of itself.

Also check TRIGGERS - 3
Jun 13 '07 #1
0 3232

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

Similar topics

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..
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. ...
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...
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...
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...
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...
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...
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...
by: debasisdas | last post by:
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...
by: --CELKO-- | last post by:
I need to convert a bunch of DB2 triggers to Oracle. Is there any kind of tools for this?
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: lllomh | last post by:
How does React native implement an English player?

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.