TRIGGERS

ORACLE OR SQL SERVER TRIGGERS

CREATE TABLE

SYNTAX

CREATE TABLE (column definition1, column definition2);

Example

SQL> create table it_file(itemcode number(6),qty_hand number(5));
Table created.

INSERT VALUES

SYNTAX

INSERT INTO table_name(column1,column2….)VALUES (value1,value2..);

Example

SQL> insert into it_file values(1201,200);
1 row created.

CREATE TRIGGER

SYNTAX

CREATE OR REPLACE trigger
[befor/after][insert/update/delete]on
{referencing/old [as] old/new}
[for each statement/for each row][when ]
PI/SQL_block

Example

SQL> create trigger fff
2 before update on it_file for each row
3 begin
4 if:new.qty_hand<:old.qty_hand then
5 raise_application_error(-20001,'quantity on hand is less');
6 end if;
7 end;
8 /
Trigger created.

UPDATE

SQL> update it_file set qty_hand=qty_hand+200 where itemcode='1201';
1 row updated.

SQL> update it_file set qty_hand=qty_hand-200 where itemcode='1201';
update it_file set qty_hand=qty_hand-200 where itemcode='1201'
*
ERROR at line 1:
ORA-20001: quantity on hand is less
ORA-06512: at "SCOTT.FFF", line 3
ORA-04088: error during execution of trigger 'SCOTT.FFF'