Do not Delete record

You need to restrict user not to delete data from database, instead of that he mark data status as disabled. To do the same you must have "Enabled" column in all of your tables. If user want to delete record he must check that records as enabled='N'.

You must query all data in application using

Same record will be available in database but user should not view. To get this implemented you should create view on table and select all column in it like following statement.

Select * from table1 where enabled='Y';

user will only see enabled data just like he see after delete some data.

How to create Auto number functionality in Oracle

Some time we need to create MS Access like Auto number feature for Oracle tables. We can get the same functionality by using this script.

1) Create Table

CREATE TABLE activity (
activity_id NUMBER NOT NULL,
description VARCHAR2(50) NULL );


2) Create primary key constraint

ALTER TABLE activity ADD ( PRIMARY KEY (activity_id) ) ;

3) Create a sequence for table

CREATE SEQUENCE sq_activity;

4) Create before insert trigger on table

CREATE TRIGGER tbi_activity
BEFORE INSERT ON activity
FOR EACH ROW
BEGIN
IF :new.activity_id IS NULL THEN
SELECT sq_activity.nextval
INTO :new.activity_id
FROM dual;
END IF;
END;
/


5) Insert new record into activity table

Insert into activity (activity_id,description) values(null,'Test');
Insert into activity (activity_id,description) values(null,'Test 2');

6) Commit;

7) Select * from activity

8) if you have more than one tables you may use following statements to create sequences and triggers on tables.

SELECT 'create sequence sq_' || table_name || ' ;'
FROM user_tab_columns
WHERE column_id = 1 AND
data_type = 'NUMBER'
ORDER BY table_name
,column_id;




SELECT 'create trigger tbi_' || table_name ||
' before insert on ' || table_name ||
' for each row begin if :new.' || column_name ||
' is null then select sq_' || table_name ||
'.nextval into :new.' || column_name ||
' from dual; end if; end;' || chr(10) || '/'
FROM user_tab_columns
WHERE column_id = 1 AND
data_type = 'NUMBER'
ORDER BY table_name
,column_id;