Oracle SQL Trigger insert/update---

Question

Please help me with the issue, I want to write a trigger where I can insert the values into new table whenever insert/update happens in source table.

Below is the table structure from where I want to fetch data into another table.

LISTING TABLE

Name Null Type

---

LISTINGID NOT NULL VARCHAR2(28)
LISTINGMANAGERID NOT NULL VARCHAR2(28)
MANAGEAVAILABILITYFLAG VARCHAR2(1)
AVAILABILITYTEXT VARCHAR2(2000)

AREAINQUIRYFLAG VARCHAR2(1)
COUNTRYTEXT VARCHAR2(50)
STATEPROVINCETEXT VARCHAR2(50)
CITYTEXT VARCHAR2(50)
CHECKINTIME VARCHAR2(10)
CHECKOUTTIME VARCHAR2(10)
TIMEZONEID VARCHAR2(20)

PERSONALLINKURL VARCHAR2(150)

GOLDSUBSCRIPTIONSINCE DATE
AUDITPASSFLAG NOT NULL VARCHAR2(1)
MGRONLINEFLAG NOT NULL VARCHAR2(1)
ADMINAPPROVALFLAG NOT NULL VARCHAR2(1)
DELETEDFLAG VARCHAR2(1)
LASTUPDATED NOT NULL DATE
UPDATEDBY NOT NULL VARCHAR2(28)
OCA NOT NULL NUMBER(38)
SUSPENDEDFLAG NOT NULL VARCHAR2(1)
POSSIBLEFEATUREDCITYFLAG NOT NULL VARCHAR2(1)
TOTALPHOTOS NUMBER(38)
SUSPENDEDDATE DATE
OFFLINEDATE DATE
CURRENCY VARCHAR2(3)
POINTCHARGE VARCHAR2(2)
AVERAGEOFREVIEWS FLOAT(126)
NUMBEROFREVIEWS NUMBER(38)
RENTALMODEL VARCHAR2(10)
NOTHANKS VARCHAR2(1)
DIGITALSIGN VARCHAR2(50)

Need result in below table with any update or new insert from listing table.

i)listingid should contain listingid's from listing table.

ii)OFFLINE COLUMN should contain data with below condition.

AuditPassFlag = 'Y' AND AdminApprovalFlag='Y' AND MgrOnlineFlag='Y' AND DeletedFlag is NULL AND SuspendedFlag !='Y'

iii)TIMESTAMP COLUMN lastupdated date from listing table.

LISTING_LASTUPDATE TABLE

Name Null Type

---

LISTINGID NOT NULL VARCHAR2(20)

IS_OFFLINE VARCHAR2(1)

TIMESTAMP TIMESTAMP(0)

Below the trigger I am have written:

Its not working properly for where condition which means Listing is online: apart from that its work fine.

CREATE OR REPLACE TRIGGER listingLast_updated
   AFTER INSERT OR UPDATE
   ON listing
   FOR EACH ROW

DECLARE
   L_Listingid             VARCHAR2 (20);

   L_ISOFFLINE             VARCHAR2 (1);

   LASTUPDATED_TIMESTAMP   DATE;
BEGIN
   SELECT SYSDATE INTO LASTUPDATED_TIMESTAMP FROM DUAL;

   IF (    :NEW.AuditPassFlag = 'Y'
       AND :NEW.AdminApprovalFlag = 'Y'
       AND :NEW.MgrOnlineFlag = 'Y'
       AND :NEW.DeletedFlag IS NULL
       AND :NEW.SuspendedFlag != 'Y') THEN
      L_ISOFFLINE := 'Y';
   ELSE
      L_ISOFFLINE := 'N';

      INSERT
        INTO LISTING_LastUPDATED (LISTINGID, IS_OFFLINE, LASTUPDATED_TIMESTAMP)
      VALUES (:NEW.LISTINGID, L_ISOFFLINE, LASTUPDATED_TIMESTAMP);
   END IF;
END;

Answer 1

You've written:

Need result in below table with any update or new insert from listing table.

That is not what your trigger does. Take another look at your IF statement. It simplifies to:

if ... then
   ...
else
   insert ...
end if;

Your INSERT is in the ELSE clause. You will therefore execute the INSERT when the initial IF statement is true. Not "with any update or new insert".

There are a number of other things you can change:

1. The variable L_Listingid is never used, remove it.

2. There's no need to use SELECT INTO ... in order to assign a variable. This can be done as follows in the declaration block:

   lastupdated_timestamp date := sysdate;
   

   However, I see no particular need for this variable to exist, so you can remove it and simply use SYSDATE in your sole INSERT.

3. There's no need to execute an ELSE at all. Set the default value of L_ISOFFLINE to be N and then use that if the IF condition doesn't evaluate to be true.

4. Two cosmetic changes, there's no need to wrap the ANDs in brackets; I've added the trigger name to the END so it's clearer.

You've you've also written:

TIMESTAMP COLUMN lastupdated date from listing table.

You actually have a LASTUPDATED date in your LISTING table, which means you're not following your own guidelines. You should use the column instead.

Putting all this together, your trigger might look like this:

create or replace trigger listinglast_updated
   after insert or update
   on listing
   for each row
declare

   l_isoffline varchar2(1) := 'N';

begin

   if :new.auditpassflag = 'Y'
       and :new.adminapprovalflag = 'Y'
       and :new.mgronlineflag = 'Y'
       and :new.deletedflag is null
       and :new.suspendedflag != 'Y' then

      l_isoffline := 'Y';

   end if;

   insert into listing_lastupdated (listingid, is_offline, lastupdated_timestamp)
   values (:new.listingid, l_isoffline, :new.lastupdated);

end listinglast_updated;