CODEWITHSUNDEEP
sqloracle-databasestored-procedurestriggers
CoolDude21
CoolDude21

Reputation: 53

SQL: implementing a stored procedure or trigger

I'm using Sql Developer and Oracle.

I need to implement a system in a database so that Once a customer has had their supply cut off, the person or business responsible for the bill will not be able to create a new account at a different service address until such time as their arrears have been cleared. This needs to be implemented using either a Trigger or a Stored Procedure.

Here is what i have attempted

    create or replace PROCEDURE NONEWACCOUNT(newcustomerID in number, 
                                             newStatus in varchar)
                                              AS
begin

      select 
      from a2_watermeter
      where customerid = newcustomerid and Status = newStatus;

      if (newStatus = 'Inactive') then
          DBMS_OUTPUT.put_line('Can not make new account as customer has to pay his/or account');
      end if;
      end;

Below is my database.

But as I am completely new to sql, I have no clue what to change

Cheers to anyone who can shed some light on this.

My database

    a2_METERREADER

 - EMPLOYEEID
 - FIRSTNAME
 - LASTNAME

a2_READING

 - READINGID
 - METERID
 - EMPLOYEEID
 - BILLNUMBER
 - READING
 - DATERECORD

a2_Watermeter

 - METERID
 - ADDRESS
 - SUBURB
 - POSTCODE
 - STATUS
 - CUSTOMERID
 - REPLACE
 - INSTALLDATE

 a2_customer

    - customerid
    - firstname
    - lastname
    - address
    - suburb
    - postcode
    - email
    - phone
    - businessname
    - dateofbirth

 a2_bill

    - billnumber
    - address
    - suburb
    - postcode
    - customerid
    - readingid
    - amount
    - reading
    - firstname
    - lastname
    - paid
    - duedate

Upvotes: 0

Views: 543

Answers (3)

jsj
jsj

Reputation: 9381

CREATE OR REPLACE PROCEDURE 
    procname
IS
    rec a2_watermeter%ROWTYPE;
    CURSOR cur
        IS
            SELECT
                *
            FROM
                a2_watermeter
            WHERE
                status = 'active'
            FOR UPDATE;
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO rec;
        EXIT WHEN cur%NOTFOUND;

        IF (SYSDATE - (
                  SELECT MIN(duedate) 
                  FROM a2_bill b 
                  WHERE b.customerid = rec.customerid AND b.paid = FALSE
                  GROUP BY(customerid, paid)
                  )) > 60 
        THEN
            UPDATE
                a2_watermeter w
            SET
                status = 'inactive'
            WHERE CURRENT OF cur;
        END IF;
    END LOOP;
    CLOSE cur;

END;

Upvotes: 1

Rob van Laarhoven
Rob van Laarhoven

Reputation: 8905

You could create a view with a computed value status. This you can use in your procedure and use in other places. This way the business logic is in one place Something like this:

create view customerstatus  
select c.customerid
,      decode(b.custimerid,null,'Active','Inactive') as status
from customer c
left outer join bill b on (b.customerid = c.custimerid)
where b.payed = 'N'
and b.duedate <= (sysdate - interval '60' day);

Upvotes: 1

APC
APC

Reputation: 146219

You need to write a stored procedure, not a trigger. The stored procedure can be called from a scheduled job, probably once a day. Triggers fire in response to DML activity, which is not predictable.

You haven't really provided much information, so it's pretty hard to give you a definition solution. Besides it's not our role on SO to do your job for you. But the core of the stored procedure will be a query like this:

select *
from a2_bill
where paid = 'NO'
and duedate <= (sysdate - interval '60' day)

Upvotes: 1

Related Questions