Triggers Samples :#Automatic date ---> #next auto increment--># Fail at : try to change Values

  Hello whats up internet ...  once again welcome back to DBMS rider ..        
this is raz... in this session we'll discuss about .. triggers ...

#Creation of table

  CREATE TABLE cities
  (
     id NUMBER(10) PRIMARY KEY,
     name VARCHAR2(90),
     created DATE
  ); 


  ... Trigger 4# automatic## get date ....

    CREATE OR REPLACE TRIGGER tr_cities_insert BEFORE INSERT ON cities 
      FOR EACH ROW
  BEGIN
    :new.created := SYSDATE;
  END;
  /



Then .. See what ;; be u r output :


Insert into cities(id,name) values(1,’hyd’);


Thumbs up .... 



// TRIGGER FOR AUTO INCREMENT ..

 Simply -?
Auto increment  is a sequence .... 

So - simple fun here > creating a sequence here ...that s it 


 CREATE SEQUENCE cities_seq START WITH 1 INCREMENT BY 1; 

Over .... How hard .... the  above code issss ...just  Increment  by 1 ... ☺hmnn

// now a simple thought ... Sequence is over .na
Then damn .. Just assigning trigger for sequence ...my work'll over 


##  trigger  for assigning auto id 

 CREATE OR REPLACE TRIGGER cities_id_tr BEFORE INSERT ON cities
  FOR EACH ROW
  BEGIN
     IF :new.id IS NULL THEN
        SELECT cities_seq.nextval INTO :new.id FROM dual;
     END IF;
  END;
  /

Then what should v do ... Think think think think ... Stop it 

Hello ... My brain Eaters ... 

Don’t think just check o/p:

 INSERT INTO cities(name) VALUES('London');

SEE ... .. Give me a thumbs up .... 


///## YESTER DAY...MY BOSS GIVEN THE WORK 
BUT I WAS HANGOUT WITH WHATS UP ..
 HARDLY ... I HAD MY BREAKFAST AND DONE MY WORK ....

BUT... Shit.... here the date is automatically created ... Then i tried many times update the system generated date ..... But ....no use ... 

My boss has a dog eye on me ... He developed a trigger for me .... 
   
# trigger 
CREATE OR REPLACE TRIGGER cities_id_tr BEFORE INSERT OR UPDATE ON cities
  FOR EACH ROW
  BEGIN
     -- Check for INSERT operation
     IF INSERTING THEN
        -- Assign ID by default
        IF :new.id IS NULL THEN
          SELECT cities_seq.nextval INTO :new.id FROM dual;
        END IF;

        -- Override created date
        :new.created := SYSDATE;
      END IF;

      -- Check for UPDATE operation
      IF UPDATING THEN
        :new.created := :old.created;
      END IF;
  END;
  /

See whats my position ...    
INSERT INTO cities (name) VALUES ('London'); 

 UPDATE cities SET created = TO_DATE('2015-03-19', 'YYYY-MM-DD');

Now give a thumbs up to BOSS 
If u like my stuff just share ..for u r social followers  ..