I want to create a trigger, where in data insertion for the same userid, trigger updates a quantity by 1, of particular row in another table.

CREATE OR REPLACE TRIGGER test_after_insert
AFTER INSERT
   ON visitor
   FOR EACH ROW
WHEN (old.visresid = new.visresid)

BEGIN
   UPDATE visqty
   SET primaryvisqty = primaryvisqty   1
   WHERE old.visresid = new.visresid
DBMS_OUTPUT.put_line ('Table updated');
END;

CodePudding user response：

Should be

CREATE OR REPLACE TRIGGER test_after_insert
   AFTER INSERT ON visitor
   FOR EACH ROW
BEGIN
   UPDATE visqty
   SET primaryvisqty = primaryvisqty   1
   WHERE visresid = :new.visresid;
END;

• remove WHEN clause
• fix WHERE clause
• terminate UPDATE statement with a semi-colon
• remove DBMS_OUTPUT call (clients, that don't support it, won't see anything anyway)

With sample tables:

SQL> CREATE TABLE visitor
  2  (
  3     visresid   NUMBER
  4  );

Table created.

SQL> CREATE TABLE visqty
  2  (
  3     visresid        NUMBER,
  4     primaryvisqty   NUMBER
  5  );

Table created.

As trigger updates the visqty table, it means that it is pre-populated with data:

SQL> INSERT INTO visqty (visresid, primaryvisqty)
  2       VALUES (1, 0);

1 row created.

Trigger:

SQL> CREATE OR REPLACE TRIGGER test_after_insert
  2     AFTER INSERT
  3     ON visitor
  4     FOR EACH ROW
  5  BEGIN
  6     UPDATE visqty
  7        SET primaryvisqty = primaryvisqty   1
  8      WHERE visresid = :new.visresid;
  9  END;
 10  /

Trigger created.

Testing:

SQL> INSERT INTO visitor (visresid)
  2       VALUES (1);

1 row created.

SQL> SELECT * FROM visqty;

  VISRESID PRIMARYVISQTY
---------- -------------
         1             1

SQL>

If visqty isn't pre-populated, use merge instead of update:

SQL> CREATE OR REPLACE TRIGGER test_after_insert
  2     AFTER INSERT
  3     ON visitor
  4     FOR EACH ROW
  5  BEGIN
  6     MERGE INTO visqty a
  7          USING (SELECT :new.visresid AS visresid FROM DUAL) b
  8             ON (a.visresid = b.visresid)
  9     WHEN MATCHED
 10     THEN
 11        UPDATE SET a.primaryvisqty = a.primaryvisqty   1
 12     WHEN NOT MATCHED
 13     THEN
 14        INSERT     (visresid, primaryvisqty)
 15            VALUES (:new.visresid, 1);
 16  END;
 17  /

Trigger created.

SQL>

CodePudding user response：

:old.visresid will always be NULL on an INSERT so your filter WHERE :old.visresid = :new.visresid (correcting the missing : on the :NEW and :OLD bind variables) will never match anything.

If you want to add one to the row in visqty for each row added to visitor then you can use:

CREATE OR REPLACE TRIGGER test_after_insert
  AFTER INSERT ON visitor
  FOR EACH ROW
BEGIN
  UPDATE visqty
  SET    primaryvisqty = primaryvisqty   1;
END;
/

db<>fiddle here

As I have boolean condition, and I would insert a data to particular field based on 0 or 1, how I could then achieve this? For example, if primvis = 1 then SET primaryvisqty = primaryvisqty 1, if primvis = 0, then instead non primaryvisqty = primaryvisqty 1?

CREATE OR REPLACE TRIGGER test_after_insert
  AFTER INSERT ON visitor
  FOR EACH ROW
BEGIN
  IF :NEW.primvis = 1 THEN
    UPDATE visqty
    SET    primaryvisqty = primaryvisqty   1;
  ELSE
    UPDATE visqty
    SET    nonprimaryvisqty = nonprimaryvisqty   1;
  END IF;
END;
/

db<>fiddle here

You could use a trigger:

CREATE OR REPLACE TRIGGER test_after_insert
  AFTER INSERT ON visitor
  FOR EACH ROW
BEGIN
  MERGE INTO visqty a
  USING DUAL b
  ON (a.permvisid = :NEW.visresid)
  WHEN MATCHED THEN
    UPDATE
    SET primaryvisqty = primaryvisqty   :new.isprimary,
        othervisqty   = othervisqty     1 - :new.isprimary
  WHEN NOT MATCHED THEN
    INSERT (permvisid, primaryvisqty, othervisqty)
    VALUES (:new.visresid, :new.isprimary, 1 - :new.isprimary);
END;
/

But it seems that what you really want is a view and not a table and a trigger:

CREATE VIEW visqty(permvisid, primaryvisqty, othervisqty) AS
SELECT r.rid,
       COUNT(CASE v.isprimary WHEN 1 THEN 1 END) AS primaryvisqty,
       COUNT(CASE v.isprimary WHEN 0 THEN 1 END) AS othervisqty
FROM   residents r
       LEFT OUTER JOIN visitor v
       ON (r.rid = v.visresid)
GROUP BY r.rid

db<>fiddle here