Click here to Skip to main content
15,892,161 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 19: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'; 
 
Share this answer
 
v2

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

  Print Answers RSS


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