Home » SQL & PL/SQL » SQL & PL/SQL » what wrong here? (xp)
what wrong here? [message #409975] Wed, 24 June 2009 11:20 Go to next message
crackgeek
Messages: 3
Registered: June 2009
Junior Member
Here i have two tables
1> attendenace--column-->empid,indate,outdate..............
2> employees--column---> empid..........

i AM required to insert all the employee id's(frm employee table) into attendance table that are not present attendace table for given indate(date)
as other column for the newly inserted empid will be allocated with nulls i have to convert them to 0's

Now the problem is if i run this trigger after the first insert all the other employees are set to zeros so i probably have to run a time based trigger which i am not aware of can u help me:

i have come this far please make the correction for the code:

---------------------------------------------------------------------------
CODE
----------------------------------------------------------------------------

CREATE TRIGGER test AFTER INSERT on attendance
FOR EACH ROW
BEGIN

insert into attendance(empid,indate) values(employee.empid,sysdate) 
where empid= (select empid from employee 
where empid not in(select empid from attendence where indate=sysdate)) ;

UPDATE table attendance SET field=0 WHERE field IS NULL;

end;

------------------------------------------------------------------------------------

[Updated on: Wed, 24 June 2009 23:16] by Moderator

Report message to a moderator

Re: what wrong here? [message #409976 is a reply to message #409975] Wed, 24 June 2009 11:32 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

And what error are you getting ? And little curious,what you are trying to do in Trigger ?
Re: what wrong here? [message #409978 is a reply to message #409975] Wed, 24 June 2009 11:41 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
The syntax of your update is wrong - the keyword table should not be there.

But once you've fixed that you're going to run straight into a "table is mutating" error.

You can't do this logic from a trigger.

You can set the field to 0 where it's null but you should do it as a straight assignment in a before insert trigger - don't use an update statement.


The insert logic should not be in a trigger at all.
Re: what wrong here? [message #409980 is a reply to message #409975] Wed, 24 June 2009 11:52 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Actually the syntax of the insert is wrong as well.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#i2163698

That needs to rewritten as a proper insert select and moved somewhere other than the trigger - suggest a procedure.
Re: what wrong here? [message #409985 is a reply to message #409980] Wed, 24 June 2009 12:31 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
cookiemonster wrote on Wed, 24 June 2009 12:52
Actually the syntax of the insert is wrong as well.




Not only is the insert syntax incorrect, the logic of indate=sysdate will not work as sysdate is current date/time and indate will no doubt be some point in the past.
Re: what wrong here? [message #410034 is a reply to message #409985] Wed, 24 June 2009 22:47 Go to previous messageGo to next message
crackgeek
Messages: 3
Registered: June 2009
Junior Member
thanks for the reply

So, i wanted split this program into two part(procedure) which will running at the end of day using some schedular or cron tab:

now please check whether this satisfies the logic:

create procedure test
xempid employee.empid%type;
xdate date;

cursor c1 select empid,sysdate from employee where empid not in(select empid from attendence where indate=sysdate;

begin
open c1;
fetch into xempid,xdate;
insert into attendance(empid,indate) values(xempid,xdate)
close c1;
end;

create procedure test2
begin
UPDATE attendance SET field=0 WHERE field IS NULL;
end;

[Updated on: Wed, 24 June 2009 22:58]

Report message to a moderator

Re: what wrong here? [message #410035 is a reply to message #409975] Wed, 24 June 2009 22:54 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: what wrong here? [message #410036 is a reply to message #410035] Wed, 24 June 2009 23:01 Go to previous messageGo to next message
crackgeek
Messages: 3
Registered: June 2009
Junior Member
thanks for the reply

So, i want to split this program into two part(procedure) which will be running at the end of day using some schedular or cron tab:

now please check whether this satisfies the logic:

create procedure test
xempid employee.empid%type;
xdate date;

cursor c1 is select empid,sysdate from employee where empid not in(select empid from attendence where indate=sysdate;

begin
open c1;
fetch c1 into xempid,xdate;
insert into attendance(empid,indate) values(xempid,xdate)
close c1;
end;

create procedure test2
begin
UPDATE attendance SET field=0 WHERE field IS NULL;
end;

[Updated on: Wed, 24 June 2009 23:06]

Report message to a moderator

Re: what wrong here? [message #410041 is a reply to message #409975] Wed, 24 June 2009 23:07 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
Please post DDL for tables.

You should only post SQL with valid/correct syntax.

The Posting Guideline also request DML for test data & to explain or show expected/desired results.
Re: what wrong here? [message #410098 is a reply to message #409975] Thu, 25 June 2009 03:55 Go to previous message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well your insert is only going to insert one record, I assume it should be inserting multiple records.
So you need to either:
a) use an insert select
b) use a for loop

And as joy division pointed out you haven't allowed for the fact that dates have time components. Have a look at the trunc function.
Previous Topic: DBMS_PIPE Error
Next Topic: IN Clause with 100 or more items
Goto Forum:
  


Current Time: Mon Dec 05 06:39:39 CST 2016

Total time taken to generate the page: 0.06897 seconds