USING TRIGGER

Database triggers or triggers are SQL statements that are stored in the database catalog. Once triggers are activated by database events such as UPDATE, DELETE or INSERT, the triggers will execute either before or after the event that initiates them.In ORACLE how you can create triggers and use them...

create table employees
( id number primary key,
name varchar2(50));

create or replace trigger uppername
before insert or update on employees
for each row
begin
:new.name:=upper(:new.name);
end;
/

insert into employees values(1, 'Jubayer');

select * from employees;

create or replace trigger uppername
before insert or update on employees
for each row
begin
if inserting then
:new.name:=upper(:new.name);
elsif updating then
:new.name:=lower(:new.name);
end if;
end;
/

insert into employees values(2, 'khalid');

<<<<<<<<<<<<<<<<update>>>>>>>>>>>>>>>>>>>

create or replace trigger uppername
before insert or update on employees
for each row
begin
if inserting then
:new.name:=upper(:new.name);
elsif updating then
:new.name:=lower(:old.name);
end if;
end;
/

insert into employees values(3, 'Shoaib');

update employees set name='Amin' where id=3;



create table log(
log_data varchar2(50),
username varchar2(50),
timeofaccess date);

create or replace trigger uppername
before insert or update on employees
for each row
begin
if inserting then
:new.name:=upper(:new.name);
elsif updating then
insert into log values('updating', sys_context('userenv', 'session_user'), sysdate);
end if;
end;
/



create or replace trigger uppername
before insert or update on employees
for each row
begin
if inserting then
:new.name:=upper(:new.name);
elsif updating then
update employees set name='Sukarna' where id=1;
end if;
end;
/


create or replace trigger uppername
before insert or update on employees
begin
if updating then
update employees set name='Sukarna' where id=1;
end if;
end;
/


create or replace trigger uppername
before insert or update on employees
declare
temp_id number;
begin
if updating then
select max(id) into temp_id from employees;
insert into employees values(temp_id, 'Sayeed');
end if;
end;
/




create or replace trigger uppername
before insert or update on employees
declare
temp_id number;
begin
if updating then
select max(id) into temp_id from employees;
insert into employees values(temp_id+1, 'Sayeed');
end if;
end;
/


****************Can't
create or replace trigger id_gen
after insert on employees
for each row
declare
temp_id number;
begin
select max(id) into temp_id from employees;
:old.id:=temp_id+1;
end;
/


create or replace trigger id_gen
after insert on employees
for each row
declare
temp_id number;
begin
select max(id) into temp_id from employees;
:new.id:=temp_id+1;
end;
/

create or replace trigger id_gen
before insert on employees
for each row
declare
temp_id number;
begin
select max(id) into temp_id from employees;
:old.id:=temp_id+1;
end;
/


create or replace trigger id_gen
before insert on employees
for each row
declare
temp_id number;
begin
select max(id) into temp_id from employees;
:new.id:=temp_id+1;
end;
/

insert into employees values(12, 'Abu');


create or replace trigger id_gen
before insert on employees
for each row
declare
temp_id number;
begin
if :old.id is null then
select max(id) into temp_id from employees;
:new.id:=temp_id+1;
end if;
end;
/

create or replace trigger id_gen
before insert on employees
for each row
declare
temp_id number;
begin
if :new.id is null then
select max(id) into temp_id from employees;
:new.id:=temp_id+1;
end if;
end;
/



create or replace trigger after_create
after create on database
begin
insert into log values(ora_sysevent, sys_context('userenv', 'session_user'), sysdate);
end;
/


create or replace trigger logon_trigg
after logon on database
begin
insert into log values(ora_sysevent, sys_context('userenv', 'session_user'), sysdate);
end;
/