Click here to Skip to main content
15,123,118 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
I was trying to create the following trigger but it's giving me warning :
Trigger created with compilation error on select object_name ,user into name,p_us from user_objects where object_type='TABLE';


SQL
create table track_cr_dr
(
userid varchar(20),
ob_name varchar(50),
action varchar(8),
ac_time varchar(50),
ac_date date 
);

create or replace trigger track_create 
after create or drop on DATABASE
declare
	ac varchar(8);
	name varchar(50);
	p_date date;
	p_time varchar(50);
	p_us varchar(20);
begin
	select sysdate into p_date from dual;
	select to_char(sysdate,'HH:MM:SS') into p_time from dual;
	select object_name ,user into name,p_us from user_objects where object_type='TABLE'; 
	
	if 'creating' then
            ac:='CREATE';
	else
            ac:='DROP';
	end if;
	
	insert into track_cr_dr values(p_us,name,ac,p_time,p_date);
end;

Thanks in advance.
Posted
Updated 4-Jan-15 20:51pm
v2

1 solution

user is a reserved keyword in Oracle.
Try to change this line
SQL
select object_name ,user into name,p_us from user_objects where object_type='TABLE';

to this
SQL
select object_name, "user" into name, p_us from user_objects where object_type = 'TABLE'; 
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900