Trigger interview question and answer

If you have two table, Table1(Sales) and Table2(SalesOrder). Table1 containing Sales Item details and Table2 containing Sales Item Order details.

Create a trigger on Table2 and update total Sales done of each Item in Table1.

--Oracle

create table Table1

(

  Table1Id number,

  Total number

)

create table Table2

(

  Table2Id number,

  Table1Id number

)

insert into Table1 values(1,0);

insert into Table1 values(2,0);

insert into Table1 values(3,0);

select * from Table1;

CREATE OR REPLACE TRIGGER Table2_tr

AFTER INSERT ON Table2

  FOR EACH ROW

BEGIN

  update Table1 set Total = (Total + 1) where Table1Id = :new.Table1Id;

END;

insert into Table2 values(1,1);

select * from Table2;

--SQL

create table Table1

(

  Table1Id int,

  Total int

)

create table Table2

(

  Table2Id int,

  Table1Id int

)

insert into Table1 values(1,0);

insert into Table1 values(2,0);

insert into Table1 values(3,0);

select * from Table1;

CREATE TRIGGER Table2_tr ON Table2

AFTER INSERT

AS

BEGIN

  declare @Table1Id int

  select @Table1Id = Table1Id from inserted

  update Table1 set Total = (Total + 1) where Table1Id = @Table1Id;

END;

insert into Table2 values(1,1);

select * from Table2;