Home » SQL & PL/SQL » SQL & PL/SQL » Trigger Help (Oracle 9.0.2 Windows XP)
Trigger Help [message #431742] Thu, 19 November 2009 06:13 Go to next message
namb
Messages: 35
Registered: September 2009
Member
I am getting the following errors

18/2: PL/SQL sql statement ignored
19/24: PL/SQL ORA-00933 SQL command not properly ended

Here is the code


CREATE or REPLACE TRIGGER emp_det_tgr
AFTER INSERT OR UPDATE
ON musterdata
FOR EACH ROW
DECLARE
cursor C1
is
select EID,MUSTERDATE,IN_TIME,OUT_TIME,OT,VC_SHIFT_CODE FROM MUSTERDATA;
v_eid musterdata.EID%TYPE;
v_date musterdata.MUSTERDATE%TYPE;
v_in musterdata.IN_TIME%TYPE;
v_out musterdata.OUT_TIME%TYPE;
v_ot musterdata.OT%TYPE;
v_sc musterdata.VC_SHIFT_CODE%TYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO v_eid,v_date,v_in,v_out,v_ot,v_sc;
EXIT WHEN C1%NOTFOUND;
IF (updating) THEN
IF v_in BETWEEN '6:30' and '16:30' THEN
update atten_det set
vc_first_leave = 'P' and
vc_second_leave = 'P' and
num_time_in = v_in and
num_time_out = v_out and
vc_shift_code = v_sc and
extra_hrs_woff = v_ot and
dt_atten = v_date;
END IF;
END IF;
END LOOP;
CLOSE C1;


end;
/

Many thanks
Re: Trigger Help [message #431746 is a reply to message #431742] Thu, 19 November 2009 06:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How can something be between '6:30' and '16:30'?
It means it has to be a string, starting with a character between 6 and 1..

SQL> select 1 from dual where '7:30' between '6:30' and '16:30';

no rows selected


Furthermore:
- you cannot select from the same table you define your row-level trigger on.
- why do you check for updating INSIDE the loop?
- read up on cursor loops. Don't use explicit open-fetch-close constructs
- no where clause in your update statement means every row will be updated
- if you want to update multiple columns, separate them with commas, not with "and"

[Updated on: Thu, 19 November 2009 06:21]

Report message to a moderator

Re: Trigger Help [message #431753 is a reply to message #431746] Thu, 19 November 2009 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
...And to add a purely form to post:
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals (the real one 9.0.2 never existed).

Regards
Michel
Re: Trigger Help [message #431756 is a reply to message #431753] Thu, 19 November 2009 06:39 Go to previous messageGo to next message
namb
Messages: 35
Registered: September 2009
Member
Dear Michel

Thanks a lot for your reply

--you cannot select from the same table you define your row-level trigger on.

I only have 2 tables atten_det and musterdata where should i apply trigger on then as i have to update atten_det every time a row is updated in musterdata.

- why do you check for updating INSIDE the loop?

I will correct that will use cursor for loop.

- no where clause in your update statement means every row will be updated

Got that idea will remove the if loop and update that in the where clause

- if you want to update multiple columns, separate them with commas, not with "and

I want to update the columns whose values is between the time 6:30 am and 4:30 pm. Please help on this
Re: Trigger Help [message #431758 is a reply to message #431756] Thu, 19 November 2009 06:40 Go to previous messageGo to next message
namb
Messages: 35
Registered: September 2009
Member
Thanks to Frank a lot too
Re: Trigger Help [message #431761 is a reply to message #431756] Thu, 19 November 2009 06:45 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
namb wrote on Thu, 19 November 2009 12:39

--you cannot select from the same table you define your row-level trigger on.

I only have 2 tables atten_det and musterdata where should i apply trigger on then as i have to update atten_det every time a row is updated in musterdata.


Well I wouldn't use a trigger at all but rather a stored procedure that modifies both tables. But if you really want to use a trigger then you probably only want to concern yourself with the current row being inserted/updated in the musterdata table. In wwhich case you can use :new.<column_name> to get the current value for each column instead of selecting it.
If you want to know why you can't use a select read up on mutating table.

namb wrote on Thu, 19 November 2009 12:39

I want to update the columns whose values is between the time 6:30 am and 4:30 pm. Please help on this


What's the datatype of in_time?
Re: Trigger Help [message #431763 is a reply to message #431761] Thu, 19 November 2009 06:47 Go to previous messageGo to next message
namb
Messages: 35
Registered: September 2009
Member
Datatype or in_time is varchar(5)

Thanks
Re: Trigger Help [message #431764 is a reply to message #431756] Thu, 19 November 2009 06:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
namb wrote on Thu, 19 November 2009 13:39
Dear Michel

Thanks a lot for your reply

--you cannot select from the same table you define your row-level trigger on.

I only have 2 tables atten_det and musterdata where should i apply trigger on then as i have to update atten_det every time a row is updated in musterdata.

Why do you think you have to select? You can use :new/:old variables.

Quote:
- why do you check for updating INSIDE the loop?

I will correct that will use cursor for loop.

That is not what I said. You first enter a loop, and then, for each iteration check if the trigger fired because of an UPDATE. You'd better check that outside the loop.
Better yet, since you do nothing in case the trigger fires due to an insert, remove the on insert clause. Then you can also remove the "if updating"
Quote:

- no where clause in your update statement means every row will be updated

Got that idea will remove the if loop and update that in the where clause

That does not make sense to me, but go ahead and post your code here.
Quote:

- if you want to update multiple columns, separate them with commas, not with "and

I want to update the columns whose values is between the time 6:30 am and 4:30 pm. Please help on this

Convert them to a time (use to_date for that) and then compare.
Re: Trigger Help [message #432765 is a reply to message #431764] Thu, 26 November 2009 03:13 Go to previous messageGo to next message
uzleuven
Messages: 16
Registered: November 2009
Junior Member
Hi,

I'm having more or less a similar problem...
I want to select from several columns from several tables several values (right joins) in a database each time a row is inserted in a certain table. How would you do this? (:NEW is not useful anymore in this case i think)

Best regards,


Jan

SELECT user_account.user_name , study_subject.label as label, study.unique_identifier as study_uid, subject.unique_identifier as subject_uid INTO user_name, label, study_uid, subject_uid FROM user_account RIGHT JOIN subject ON user_account.user_id = subject.owner_id RIGHT JOIN study_subject ON subject.subject_id = study_subject.subject_id RIGHT JOIN study ON study.study_id = study_subject.study_id WHERE study_subject.study_subject_id = ssid AND study.unique_identifier LIKE '%DOPPLER_%' order by study_subject.subject_id;

[Updated on: Thu, 26 November 2009 03:14]

Report message to a moderator

Re: Trigger Help [message #432837 is a reply to message #432765] Thu, 26 November 2009 10:15 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I probably would not do it; since you apparently can generate the data to be inserted from data already present, you should be able to retrieve the data at a later moment without the insert now.
IF however I had to do it, I would reconsider if I would do it in a trigger. I would prefer to do it in the procedure that decides/takes care of the initial insert.
IF I had to do it in a trigger, I would probably use a generic package that would be called from all triggers. It would either contain a separate query for each triggering table (leaving out that table from the query; I can use the parameters I sent from my trigger), or I would use dynamic SQL to create these separate queries. It all depends on the volumes involved (dynamic sql scales pretty badly)
Previous Topic: to_char(interval)
Next Topic: Oracle schedule job
Goto Forum:
  


Current Time: Sat Oct 01 02:33:19 CDT 2016

Total time taken to generate the page: 0.13631 seconds