How to create Trigger in Sql Server(Referential Integrity) Part 2

 Why creating all Trigger and Table 

1(a) When user insert id and Name in CusDetails  Table then id should be insert into CusTotalItem  Table and TotalItem  value which is 0 by default this work is  done with the  help of TRIGGER whose name is tr0.

1(b) When user insert id and Name in CusDetails  Table then id should be insert into CusItems  Table and Items  value which is 0 by default when id is  not present in CusItems  Table ,if id is present in CusItems  Table then Items  value only update with 0,  this work is  done with the  help of TRIGGER whose name is mytr.

Note both trigger tr0 and mytr on fire  on CusDetails

2  After this

create CusItems Table which contains all item   information with id which is also present in other two tables ie CusDetails and CusTotalItem. If user insert or delete any Item in CusItems table with its id then it will reflect on the table CusTotalItem because of the TRIGGER which name is mytr2

Note  trigger mytr2  on fire  on CusItems

First we  create  a  table CusDetails in tests database

use tests

create table CusDetails(id int ,Name varchar(50))

after we  create  a  table CusItems in tests database

use tests

create table CusItems(id int ,Item int)

after  we  create  a other   table CusTotalItem in tests database

use tests

create  table CusTotalItem(id int ,TotalItem int)

 insert into CusDetails values(1,'Lokendra')

 insert into CusDetails values(2,'Dinesh')

  insert into CusItems values(1,30)

  insert into CusItems values(2,35)

  Select * from CusDetails

  Select * from CusItems

after   create  table we create  trigger  tro on CusDetails

create trigger tr0 on CusDetails

after insert

as

declare @id int

Select @id=id from inserted

insert into CusTotalItem values(@id,0)

insert  into CusDetails values(1,'Jitendra')

select * from CusDetails

select * from CusTotalItem

after   create  table we create  trigger  mytr on CusDetails

create trigger mytr on CusDetails

  after insert

  as

  declare @id int

  select @id=id from inserted

  if exists(Select * from CusItems where id=@id)

  begin

  update CusItems set item=0 where id=@id

  print 'Data Update'

  end

  else

  begin

  insert into CusItems values(@id,0)

  print 'Inserted'

  end

insert into CusDetails values(3,'Kush Tiwari')

insert into CusDetails values(3,'Kush Tiwari')

insert into CusDetails values(1,'Lokendra')

  Select * from CusDetails

  Select * from CusItems

 after   create  table we create anoter trigger  mytr2 on  CusItems

  

  alter trigger mytr2 on CusItems

  after insert,delete

  as

  if exists (select * from inserted )

  update CusTotalItem set TotalItem=TotalItem + t.item from inserted t where          CusTotalItem.id=t.id

  else

  update CusTotalItem set TotalItem=TotalItem - t.item from deleted t where   CusTotalItem.id=t.id

cheacking all trigger...............

 insert into CusDetails values (1,'Ken')

  insert into CusItems values(1,50)

  insert into CusItems values(1,30)

select * from CusTotalItem