Home » SQL & PL/SQL » SQL & PL/SQL » Tricky Trigger
Tricky Trigger [message #206605] Thu, 30 November 2006 15:31 Go to next message
dapimp18
Messages: 7
Registered: November 2006
Junior Member
Hi,

I am having a bit of a problem creating a trigger to pick out some info from my linker table after update,

T_ID ML_ID DATE A
----- ----- --------- -
T01 L01 10-OCT-06 Y
T01 L02 12-OCT-06 Y
T02 L03 14-OCT-06 N
T03 L01 10-OCT-06 Y
T04 L01 10-OCT-06 N
T04 L03 14-OCT-06 Y
T04 L04 28-OCT-06 N

7 rows selected.

Ill like my trigger after update to pull up any T_id that as 2 or more N under A. From the table above ill like the trigger to output a msg show T04 has two N's if that is possible plz help, feel free to ask questions

Thanks
Re: Tricky Trigger [message #206606 is a reply to message #206605] Thu, 30 November 2006 15:53 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
If you want help, paste what you have tried into the thread. We will help, but won't do the work for you.
Re: Tricky Trigger [message #206609 is a reply to message #206606] Thu, 30 November 2006 16:37 Go to previous messageGo to next message
dapimp18
Messages: 7
Registered: November 2006
Junior Member
create or replace trigger atten
after insert or update of st_id, ml_id, attendance on stml
DECLARE

v_st stml.st_id%TYPE;
v_ml stml.ml_id%TYPE;
v_a stml.attendance%TYPE;
BEGIN
for pnt in (select st_id, ml_id, attendance
INTO v_st, v_ml, v_a
from stml
where v_a = 'N')
LOOP
DBMS_OUTPUT.PUT_LINE( pnt.st_id || ' ' || pnt.attendance);
END LOOP;
END;
/

hi bill im not looking for an answer ill like to understand it because i might have to explain the code. You dont have to write a new code just tell me where im going wrong

Thanks
Re: Tricky Trigger [message #206655 is a reply to message #206609] Fri, 01 December 2006 00:32 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Trigger you wrote wouldn't compile - do not use INTO clause in FOR loop - columns that are selected are automatically assigned to cursor variable. Therefore, you don't need to declare variables either. Besides that, this code doesn't do what you wrote in your first post: "pull up any T_id that as 2 or more N under A".

Furthermore, writing this code in a trigger will be visible only when you run those updates and/or inserts through SQL*Plus and enable SERVEROUTPUT. This will not be visible to anyone who uses, for example, form (written using Forms Builder).

But OK, that's another question. Here's a trigger which might do what you need:
CREATE OR REPLACE TRIGGER trg_atten
  AFTER INSERT OR UPDATE ON stml
BEGIN
  FOR cur_r IN (SELECT t_id, COUNT(*) cnt
                FROM stml
		    WHERE attendance = 'N'
		    GROUP BY t_id
		    HAVING COUNT(*) >= 2)
  LOOP
    dbms_output.put_line(cur_r.t_id || ' - ' || cur_r.cnt);
  END LOOP;
END;
/
Re: Tricky Trigger [message #206766 is a reply to message #206655] Fri, 01 December 2006 07:05 Go to previous message
dapimp18
Messages: 7
Registered: November 2006
Junior Member
Thanks for the tip Littlefoot, i wouldnt use into clause anymore.
Previous Topic: something about EXENT MANAGEMENT and SEGMENT SPACE MANAGEMENT
Next Topic: Extract Year and Month
Goto Forum:
  


Current Time: Thu Dec 08 10:04:10 CST 2016

Total time taken to generate the page: 0.10271 seconds