Table creation:
Create Table user_info (
Id integer not null,
Username varchar (30) not null,
Password varchar (20) not null,
Createdate date not null,
Status integer not null,
Constraint pk_user_info primary key (ID)
);
Create Table user_info_temp (
Id integer not null,
Username varchar (30) not null,
Password varchar (20) not null,
Createdate date not null,
Status integer not null,
Constraint pk_user_info_temp primary key (ID)
);
Trigger Syntax:
Create or replace trigger usertotemp after insert or update or delete
On user_info for each row
Declare
Integrity_error exception;
Errno integer;
Errmsg char (200 );
Dummy integer;
Found Boolean;
Begin
If inserting then
Insert into user_info_temp (ID, username, password, createdate, status) values (: New. ID,: New. username,: New. password,: New. createdate,: New. status );
Elsif updating then
Update user_info_temp Set ID =: New. ID, username =: New. username, password =: New. Password, status =: New. status where id =: Old. ID;
Elsif deleting then
Delete from user_info_temp where id =: Old. ID;
End if;
Exception
When integrity_error then
Raise_application_error (errno, errmsg );
End;
Test data:
Insert into user_info (ID, username, password, createdate, status) values (1, 'xier ', '20170101', to_date ('2017-10-11 ', 'yyyy-mm-dd'), 1)
Update user_info U set U. Status = 3, U. Username = 'xier 'where u. ID = 1
Delete from user_info u where u. ID = 1