-- To view a description of triggers for current user:
select description from user_triggers;

select trigger_name, description, trigger_body from user_triggers
/

CREATE TRIGGER ADMIN.TR_SEQ_ BEFORE INSERT ON ADMIN.CUST_REFERRAL
 FOR EACH ROW  BEGIN  SELECT ADMIN.SEQ_391_1.nextval INTO :new.CUST_REFERRAL_ID
 FROM dual; END;
/


ADMIN.TR_SEQ_15_1 
BEFORE INSERT ON ADMIN.CUSTOMER FOR EACH ROW  
BEGIN  
   SELECT ADMIN.SEQ_15_1.nextval INTO :new.CUSTOMER_ID FROM dual; 
END;


DROP SEQUENCE {schema}.seq_test_code; 

CREATE SEQUENCE {schema}.seq_test_code 
INCREMENT BY 1 
START WITH 1 
MINVALUE 1 NOMAXVALUE 
NOCYCLE 
NOCACHE 
NOORDER; 

Drop trigger {schema}.tbi_testtable; 
Create trigger {schema}.tbi_testtable 
BEFORE INSERT ON {schema}.testtable 
REFERENCING OLD AS OLD NEW AS NEW 
FOR EACH ROW 
BEGIN 
	if INSERTING then 
		if :new.code is null then 
			select {schema}.SEQ_test_code.nextval into :new.code from dual; 
	end if; 
END; 





CREATE or replace TRIGGER tri_client_sin
BEFORE INSERT ON client
REFERENCING OLD AS OLD NEW AS NEW 
FOR EACH ROW 
BEGIN 
	if INSERTING then 
		if :new.sin = 0 then 
			:new.sin := null
		end if;
	end if; 
END; 


-- Example of a trigger
CREATE or replace TRIGGER triu_client_sin
BEFORE INSERT OR UPDATE OF sin ON CLIENT FOR EACH ROW WHEN (new.sin = 0) 
BEGIN 
	if :new.sin = 0 then 
		:new.sin := null;
	end if;
END; 
/

-- Test the above trigger
create table testdbstar
(sin number(9));
CREATE or replace TRIGGER triu_testdbstar_sin
BEFORE INSERT OR UPDATE OF sin ON testdbstar FOR EACH ROW WHEN (new.sin = 0) 
BEGIN 
	if :new.sin = 0 then 
		:new.sin := null;
	end if;
END; 
/
insert into testdbstar values (9);
insert into testdbstar values (0);
select * from testdbstar;
update testdbstar set sin = 0 ;
select * from testdbstar;
update testdbstar set sin = 1 ;
select * from testdbstar;
rollback;
drop TRIGGER triu_testdbstar_sin;
drop table testdbstar;


IF INSERTING THEN
 INSERT INTO audit_table
    VALUES (USER || ' is inserting' ||
              ' new key: ' || :new.key);
ELSIF DELETING THEN
 INSERT INTO audit_table
    VALUES (USER || ' is deleting' ||
              ' old key: ' || :old.key);
ELSIF UPDATING('FORMULA') THEN
 INSERT INTO audit_table
    VALUES (USER || ' is updating' ||
              ' old formula: ' || :old.formula ||
              ' new formula: ' || :new.formula);
ELSIF UPDATING THEN
 INSERT INTO audit_table
    VALUES (USER || ' is updating' ||
              ' old key: ' || :old.key ||
              ' new key: ' || :new.key);
END IF;