CODEWITHSUNDEEP
sqloracle-databaseplsqltriggers
Sam Keith
Sam Keith

Reputation: 99

PLSQL condition statement in trigger involving 2 tables

I've two tables purchases and customers, I need to update visits_made (number) in customers if time of purchase ptime (date) in purchases table is different from the already existing last_visit (date) in customers table. Here is the trigger that I'm trying and I'm doing something terribly and shamefully wrong.

create or replace trigger update_visits_made
after insert on purchases
for each row
declare new_date purchases.ptime%type;

begin
  select ptime into new_date
    from purchases
  where purchases.ptime = :new.ptime;

  if new_date = customers.last_visit then
    new.last_visit=old.last_visit;
  else
    update customers 
      set visits_made=visits_made+1
    where purchases.ptime=:new.ptime;
  end if;
end;
/
show errors

Can anybody please tell me where I'm going wrong?

I get following errors LINE/COL ERROR

10/15 PLS-00103: Encountered the symbol "=" when expecting one of the following: := . ( @ % ;

11/1 PLS-00103: Encountered the symbol "ELSE"

16/1 PLS-00103: Encountered the symbol "END"

Upvotes: 0

Views: 89

Answers (2)

Avrajit Roy
Avrajit Roy

Reputation: 3303

I think somehow i get your requirement. Basically its a ticker which count the vists of user based on Login dates. I have written a snippet below which replicates the same scenario as mentioned Above. Let me know if this helps.

-- Table creation and insertion script
CREATE TABLE PURCHASES
(
P_ID NUMBER,
P_TIME DATE
);

INSERT INTO PURCHASES
SELECT LEVEL,SYSDATE+LEVEL FROM DUAL
CONNECT BY LEVEL < 10;

CREATE TABLE CUSTOMERS
(
P_ID NUMBER,
P_VISITS NUMBER
);

INSERT INTO CUSTOMERS
SELECT LEVEL,NULL FROM DUAL
CONNECT BY LEVEL < 10;

-- Table creation and insertion script

--Trigger Script

CREATE OR REPLACE TRIGGER update_purchase BEFORE
  INSERT ON purchases FOR EACH row 
  DECLARE 
  new_date purchases.p_time%type;
  BEGIN
  BEGIN
  SELECT A.P_TIME
    INTO new_date
    FROM
  (SELECT p_time,
    ROW_NUMBER() OVER(PARTITION BY P_ID ORDER BY P_TIME DESC) RNK
    --    INTO new_date
  FROM purchases
  WHERE purchases.p_id = :new.p_id
  )a
WHERE A.RNK =1;
    EXCEPTION WHEN OTHERS THEN
    RETURN;
    END;
IF :NEW.P_TIME <> new_date THEN
  UPDATE customers
  SET P_VISITS        =NVL(P_VISITS,0)+1
  WHERE p_id=:new.p_id;
END IF;
END;

--Trigger Script


--Testing Script
INSERT INTO PURCHASES VALUES
(9,TO_DATE('12/11/2015','MM/DD/YYYY'));

--Testing Script

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This is a scalar assignment in PL/SQL:

new.last_visit = old.last_visit;

Not only should this be done with := but new and old should have colons before their names:

:new.last_visit := :old.last_visit;

Once you have fixed that problem, then the update will pose an issue:

update customers 
    set visits_made=visits_made+1
    where purchases.ptime = :new.ptime;

It is unclear to me what this is supposed to be doing, so I can't suggest anything, other than pointing out that purchases is not defined.

Upvotes: 1

Related Questions