Home » SQL & PL/SQL » SQL & PL/SQL » how to create the trigger ? (oracle 11g,windows xp)
how to create the trigger ? [message #575845] Mon, 28 January 2013 06:34 Go to next message
saeed_06
Messages: 10
Registered: January 2013
Location: pakistan
Junior Member
how to create the trigger that count the last 7 day about from sale table if the last 7 day amount is greater then 10000 then update the commission 2% other wise do nothing
Re: how to create the trigger ? [message #575847 is a reply to message #575845] Mon, 28 January 2013 06:39 Go to previous messageGo to next message
Littlefoot
Messages: 19677
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Trigger won't do that; create a stored procedure and schedule it (DBMS_SCHEDULER).
Re: how to create the trigger ? [message #575851 is a reply to message #575845] Mon, 28 January 2013 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 59399
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
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" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: how to create the trigger ? [message #576076 is a reply to message #575851] Thu, 31 January 2013 06:10 Go to previous messageGo to next message
saeed_06
Messages: 10
Registered: January 2013
Location: pakistan
Junior Member
how i make the procedure please tell me ?
Re: how to create the trigger ? [message #576078 is a reply to message #576076] Thu, 31 January 2013 06:38 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member


CREATE OR REPLACE
PROCEDURE process_commission
IS
  l_salsum NUMBER;
BEGIN
  SELECT SUM(sal)
  INTO l_salsum
  FROM emp
  WHERE hiredate>=TRUNC(SYSDATE)
  AND hiredate   <TRUNC(SYSDATE-7);
  IF l_salsum    >10000 THEN
    UPDATE emp SET sal=sal+sal*0.02;
  END IF;
END;

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'process_commission_job',
    JOB_TYPE        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN process_commission; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=5; byminute=00; bysecond=0;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job defined to update the commission.');
END;
/

[Updated on: Thu, 31 January 2013 06:40]

Report message to a moderator

Re: how to create the trigger ? [message #576081 is a reply to message #576078] Thu, 31 January 2013 06:52 Go to previous messageGo to next message
saeed_06
Messages: 10
Registered: January 2013
Location: pakistan
Junior Member
one thing more tell me when we can use the declare or is ?what is difference between them? i am little bit confuse about is or declare ?
Re: how to create the trigger ? [message #576082 is a reply to message #576081] Thu, 31 January 2013 06:58 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

If you want to store permanently and want to use it again again by calling it then above needed(so it is called stored procdeure/function).

If you want to just test then you have to code like,but you can't call
declare
l_salsum NUMBER;
BEGIN
  SELECT SUM(sal)
  INTO l_salsum
  FROM emp
  WHERE hiredate>=TRUNC(SYSDATE)
  AND hiredate   <TRUNC(SYSDATE-7);
  IF l_salsum    >10000 THEN
    UPDATE emp SET sal=sal+sal*0.02;
  END IF;
END;

Re: how to create the trigger ? [message #576083 is a reply to message #576082] Thu, 31 January 2013 06:59 Go to previous messageGo to next message
saeed_06
Messages: 10
Registered: January 2013
Location: pakistan
Junior Member
thx u sir Smile
Re: how to create the trigger ? [message #576084 is a reply to message #576083] Thu, 31 January 2013 07:03 Go to previous messageGo to next message
saeed_06
Messages: 10
Registered: January 2013
Location: pakistan
Junior Member
i understand above procedure if i want to calculate the salary of employee one by one and check it then i use cursor or not ?
Re: how to create the trigger ? [message #576085 is a reply to message #576084] Thu, 31 January 2013 07:14 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Yes,For what condition you want to check the each sal of Employee based on that you need to use the cursor.And how you want to link with the above.
Re: how to create the trigger ? [message #576086 is a reply to message #576085] Thu, 31 January 2013 07:17 Go to previous messageGo to next message
saeed_06
Messages: 10
Registered: January 2013
Location: pakistan
Junior Member
WHERE hiredate>=TRUNC(SYSDATE)
what is the meaning of above condition i cannot understand ?
Re: how to create the trigger ? [message #576088 is a reply to message #576086] Thu, 31 January 2013 07:19 Go to previous messageGo to next message
saeed_06
Messages: 10
Registered: January 2013
Location: pakistan
Junior Member
if the sal is greater the 10000 check every empolyee one by one then add the commission 2% on his salary
Re: how to create the trigger ? [message #576089 is a reply to message #576086] Thu, 31 January 2013 07:22 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

And do you have the emp table structure of scott schema.otherwise can you please give the structure of Sales table.And how you are going to calcualte 7 days( on which column).

Because all the create statement of table,insertion of data is needed to test everything what you want.
Re: how to create the trigger ? [message #576091 is a reply to message #576088] Thu, 31 January 2013 07:24 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

saeed_06 wrote on Thu, 31 January 2013 18:49
if the sal is greater the 10000 check every empolyee one by one then add the commission 2% on his salary


If you want to add comission every employee then why you want to check all.At a time update statement can do all the updation.
Re: how to create the trigger ? [message #576092 is a reply to message #576089] Thu, 31 January 2013 07:26 Go to previous messageGo to next message
Michel Cadot
Messages: 59399
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@sss111ind

Please do NOT provide complete solution to beginners and learners.

Tell me and I'll forget; show me and I may remember; involve me and I'll understand

Regards
Michel
Re: how to create the trigger ? [message #576093 is a reply to message #576089] Thu, 31 January 2013 07:27 Go to previous messageGo to next message
saeed_06
Messages: 10
Registered: January 2013
Location: pakistan
Junior Member
actually my sir give me the assignment check the total_amount in sale table.if in last 7 days sale is greater then 10000 then give a 2% discount to him
attributes of sale table
s-no,customer,date_sale,amount,discount
Re: how to create the trigger ? [message #576100 is a reply to message #576082] Thu, 31 January 2013 07:57 Go to previous messageGo to next message
joy_division
Messages: 4527
Registered: February 2005
Location: East Coast USA
Senior Member
sss111ind wrote on Thu, 31 January 2013 07:58

WHERE hiredate>=TRUNC(SYSDATE)
AND hiredate <TRUNC(SYSDATE-7);
[/code]


This can never happen. How can it be greater than today but less than 7 days ago?
Re: how to create the trigger ? [message #576101 is a reply to message #576100] Thu, 31 January 2013 08:02 Go to previous messageGo to next message
saeed_06
Messages: 10
Registered: January 2013
Location: pakistan
Junior Member
u write this condition at above procedure i cannot understand ????
Re: how to create the trigger ? [message #576103 is a reply to message #576101] Thu, 31 January 2013 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 59399
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read the following topic: http://www.orafaq.com/forum/t/185956/102589/

Regards
Michel
Re: how to create the trigger ? [message #576207 is a reply to message #576103] Fri, 01 February 2013 07:29 Go to previous message
saeed_06
Messages: 10
Registered: January 2013
Location: pakistan
Junior Member
thx alot
Previous Topic: Insert 999999 records in table
Next Topic: Create table using trigger
Goto Forum:
  


Current Time: Mon Oct 20 04:56:58 CDT 2014

Total time taken to generate the page: 0.08246 seconds