Home » SQL & PL/SQL » SQL & PL/SQL » Triggers and Date's
Triggers and Date's [message #205429] Fri, 24 November 2006 13:15 Go to next message
lee337c33
Messages: 8
Registered: November 2006
Location: London
Junior Member
Hi all,

Despite having a fairly decent grasp of SQL, I'm completely new to the PL/SQL side of things. I've created a couple of triggers before to calculate and recalculate wages, salary ratios etc, but I'm having a problem firing a trigger relating to DATE. The trouble being that I don't know how to do it.

I need the system to automatically print a birthday card when it is an employee's birthday; the trigger will fire when current date = employee’s birthday. The employee's DOB has been split into 3 columns: day, month and year; obviously, no trigger would fire if all 3 were in a DOB column as it is never likely to be 1970 in the future.

As I loath the DATE data type, I wondered if it were possible to create this trigger without using DATE. Any help would be greatly appreciated, and, of course, fully acknowledged in the subsequent documentation - unless you say you don't wana!

Thanks, Lee


Re: Triggers and Date's [message #205434 is a reply to message #205429] Fri, 24 November 2006 14:57 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
First of all, 'DOB' table is badly designed - dates should be kept in DATE datatype columns, not split into three (I presume) NUMBER ones. I'd suggest you to change it (if possible).

OK, here's an example - table as I see it and some sample data. As today is 24th of November 2006, EMP_ID = 2343 celebrates his 38th birthday.
SQL> desc dob
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------

 DD                                                 NUMBER(2)
 MM                                                 NUMBER(2)
 YYYY                                               NUMBER(4)
 EMP_ID                                             NUMBER

SQL> select * From dob;

        DD         MM       YYYY     EMP_ID
---------- ---------- ---------- ----------
        11          3       1983       1234
        13          8       1970       4432
        24         11       1968       2343
PL/SQL procedure which selects people who celebrate their birthday on 'sysdate' could look like this:
BEGIN
  FOR cur_r IN (SELECT emp_id,
                       (TO_NUMBER(TO_CHAR(SYSDATE, 'yyyy')) - yyyy) no_birthdays 
                FROM DOB
                WHERE LPAD(TO_CHAR(dd), 2, '0') || 
                      LPAD(TO_CHAR(mm), 2, '0') = TO_CHAR(SYSDATE, 'ddmm')
               )
  LOOP
    dbms_output.put_line(cur_r.emp_id ||' '|| cur_r.no_birthdays);
    -- your birthday message code should go here
  END LOOP;
END;
/

2343 38

PL/SQL procedure successfully completed.

SQL>
Shortly: select employee ID and his/her age (by subtracting "this year" and "employee's year of birth") WHERE (day + month) = today's (day + month).

In this example, I've used DBMS_OUTPUT.PUT_LINE just to check whether it works or not. You'd put your "happy birthday" code there (I guess, send a mail using UTL_SMTP package).

You mentioned a trigger; perhaps you could consider using a job instead; I don't know what would be a triggering event on the 'DOB' table, but job can be scheduled to run every day and take care about it.
Re: Triggers and Date's [message #205543 is a reply to message #205434] Sat, 25 November 2006 10:53 Go to previous messageGo to next message
lee337c33
Messages: 8
Registered: November 2006
Location: London
Junior Member
Thanks Littlefoot,

The idea behind implementing a trigger was that the system needs to automatically produce a birthday card for people on their birthday; the triggering event would be when it is an emp's bday. Would your syntax above enable that or would it need to be typed in everyday to see if any of the staff have a birthday?

One more thing, what's 'Lpad'. Sorry if that sounds stupid, but I’m still pretty new to this!
Re: Triggers and Date's [message #205562 is a reply to message #205543] Sun, 26 November 2006 02:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You definitely would need a triggering event; like something happening in the table (e.g. insert, delete etc).
That is why you should not write this as a trigger, but as a daily batch job.
This has nothing to do with a trigger, since it is not related to anything changing.


lpad(A, n, x) adds extra characters 'x' to the left of A, till A consists of n characters.
Re: Triggers and Date's [message #205828 is a reply to message #205429] Mon, 27 November 2006 16:51 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Why do you not like date columns. They provide the greatest functionally of any column type to store any type of date or time information. If you had the DOB in a date, it would be simple.

where
to_char(dob,'MMDD') = TO_CHAR(SYSDATE,'MMDD')

You know know if it is their birthday. As for a trigger, the only way to do it would be by using an ON-LOGIN trigger. This will only work it each user has their own oracle login and there is a tie between the employee table and the login schema.

[Updated on: Mon, 27 November 2006 16:51]

Report message to a moderator

Re: Triggers and Date's [message #205832 is a reply to message #205828] Mon, 27 November 2006 17:36 Go to previous messageGo to next message
lee337c33
Messages: 8
Registered: November 2006
Location: London
Junior Member
Hey,

My dislike for 'DATE' data types stems solely from my inexperience with SQL. When I was building this database, for whatever reason, the Oracle wouldn't create the database due to some error with the INSERT syntax. I was also warned that it may become difficult to get anything to work if I had to specifically mention just the day and month within a single DOB column; in fact I was told it wasn't possible.

The database is already up and working so I'd rather not change it. The fields are currently called: DOB_DAY, DOB_MONTH, DOB_YEAR.

What would the complete procedure look like with these columns? I don't care if it isn't a trigger, just need it to work!

Thanks
Re: Triggers and Date's [message #205901 is a reply to message #205832] Tue, 28 November 2006 01:51 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
lee337c33
... so I'd rather not change it. The fields are currently called: DOB_DAY, DOB_MONTH, DOB_YEAR.

But you could easily add that DATE column:
ALTER TABLE dob ADD birthday DATE;

UPDATE dob SET
  birthday = TO_DATE(LPAD(TO_CHAR(dob_day), 2, '0') || 
                     LPAD(TO_CHAR(dob_month), 2, '0') ||
                     TO_CHAR(dob_year),
                     'ddmmyyyy'
                    );

Working with dates as you do brings nothing but pain and problems.
Re: Triggers and Date's [message #206092 is a reply to message #205429] Tue, 28 November 2006 13:24 Go to previous messageGo to next message
dapimp18
Messages: 7
Registered: November 2006
Junior Member
hey im having the same problem, i need to print a birthday card for students on their birthday. My table looks like this

St_id varchar2 (5) PRIMARY KEY,
LastName varchar2 (10),
FirstName varchar2 (10),
Address varchar2 (30),
Postcode varchar2 (9),
Tel_No integer,
DOB date,
Gender varchar2 (1),
Email varchar2 (30)

i created a function that displays the ages of all the student

CREATE OR REPLACE FUNCTION Age(dob DATE, p_base_date DATE := SYSDATE) RETURN NUMBER
IS
v_age NUMBER(3) := 0;
BEGIN
v_age := TRUNC(MONTHS_BETWEEN(p_base_date,dob)/12);
RETURN v_age;
END;

i then tried the procedure but it just freezes up oracle

Create or Replace procedure Age8
AS
v_first_name student.firstname%TYPE;
v_dob student.dob%TYPE;
v_last_name student.lastname%TYPE;
v_age number(15);
BEGIN
LOOP
select firstname, lastname, dob
into v_first_name, v_last_name, v_dob
from student
where to_char(dob,'DD-MON') = to_char(sysdate,'DD-MON');
END LOOP;
DBMS_OUTPUT.PUT_LINE('Happy Birthday' || v_last_name ||
v_first_name || age(v_dob));
END;
/

Can any one spot a mistake in the code and let me know because it freezes oracle. i also attempted a trigger but luck aswell

Create or replace trigger birthday
after insert or update on student
Declare
w_f varchar(10);
w_d date;
w_l varchar(10);
Begin
select firstname, lastname, dob into w_f, w_l, w_d
from student
where to_char(w_d,'DD-MON') = to_char(sysdate,'DD-MON');
DBMS_OUTPUT.PUT_LINE('Happy Birthday' || w_f ||
w_l || w_d);
END;
/

Any help would be Appreciated

Re: Triggers and Date's [message #206093 is a reply to message #206092] Tue, 28 November 2006 13:37 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is an infinite loop - that's why it seems that Oracle got frozen. Change the logic (one solution could be a cursor loop).
Re: Triggers and Date's [message #206094 is a reply to message #205429] Tue, 28 November 2006 13:37 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
You have an endless loop, write it as

Create or Replace procedure Age8
AS
v_first_name student.firstname%TYPE;
v_dob student.dob%TYPE;
v_last_name student.lastname%TYPE;
v_age number(15);
BEGIN
for pnt in (select firstname, lastname, dob
            from student
            where to_char(dob,'DD-MON') =  to_char(sysdate,'DD-MON')) loop
  DBMS_OUTPUT.PUT_LINE('Happy Birthday' || pnt.lastname ||
pnt.firstname || age(pnt.dob));
END LOOP;
END;



Make sure to do set serveroutput on before you run the procedure or dbms_output won't work.

[Updated on: Tue, 28 November 2006 13:38]

Report message to a moderator

Re: Triggers and Date's [message #206096 is a reply to message #205429] Tue, 28 November 2006 14:02 Go to previous messageGo to next message
dapimp18
Messages: 7
Registered: November 2006
Junior Member
Thanks ppl for much appreciated
Re: Triggers and Date's [message #206099 is a reply to message #205429] Tue, 28 November 2006 14:20 Go to previous messageGo to next message
dapimp18
Messages: 7
Registered: November 2006
Junior Member
One last question how do i use this with a trigger?
Re: Triggers and Date's [message #206103 is a reply to message #206099] Tue, 28 November 2006 15:01 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You should see Littlfoot's first response. A Trigger doesn't really make any sense.
Re: Triggers and Date's [message #206344 is a reply to message #206094] Wed, 29 November 2006 12:49 Go to previous message
dapimp18
Messages: 7
Registered: November 2006
Junior Member
Hi,


Thanks for replying i have attached everything i have done if you dont understand anything let me know. If it would take too much of your time than dont worry about it.

Thanks
  • Attachment: help.txt
    (Size: 2.46KB, Downloaded 182 times)
Previous Topic: Date Calculation and Conversion to Year, Month, Day format
Next Topic: Ordering/Grouping Problem With 3 Joins
Goto Forum:
  


Current Time: Tue Dec 06 06:35:12 CST 2016

Total time taken to generate the page: 0.16748 seconds