用触发器实现主从表关系(主表更改从表更改 )

 
用触发器实现的  插入 更新  删除  子表也变化
                             

CREATE   TABLE   [ dbo ] . [ tablex ]  (
     [ idx ]   [ int ]   IDENTITY  ( 1 ,  1 )  NOT   NULL  ,
     [ ProductID ]   [ int ]   NULL  ,
     [ productName ]   [ char ]  ( 10 ) COLLATE Chinese_PRC_CI_AS  NULL  
)  ON   [ PRIMARY ]
GO

CREATE   TABLE   [ dbo ] . [ tabley ]  (
     [ idy ]   [ int ]   IDENTITY  ( 1 ,  1 )  NOT   NULL  ,
     [ ProductID ]   [ int ]   NULL  ,
     [ productname ]   [ char ]  ( 10 ) COLLATE Chinese_PRC_CI_AS  NULL  
)  ON   [ PRIMARY ]
GO




CREATE   TRIGGER   triDelete  ON   [ dbo ] . [ tablex ]  
FOR     delete
AS

begin
declare   @aa   varchar ( 200 )
set   @aa = ( select   productid    from  deleted)
if   @@rowcount > 0
delete   tabley     where  productid = @aa
end





CREATE   trigger  tritmp  on  tablex  for   insert
as
insert   into  tabley(ProductID)  select  i.ProductId  from  inserted  as  i    where   i.ProductId > 100



CREATE   TRIGGER   triUpdate   ON   [ dbo ] . [ tablex ]  
FOR     UPDATE
AS
IF   UPDATE (productname)
begin
declare   @aa   varchar ( 200 )
set   @aa = ( select   productid   from  INSERTED)
declare   @bb   varchar ( 200 )
set   @bb = ( select   productname   from  INSERTED)
if  ( @@rowcount > 0 )
update   tabley   set  productname = @bb   where  productid = @aa
end






insert  tablex  values ( 300 , ' 东方 ' )

update  tablex  set  productname = ' 大海 '   where  productid = 300
select   *   from  tablex

select   *   from  tabley

delete   from  tablex  where  productid = 300
select   *   from  tablex

select   *   from  tabley

其实删除时也可以用外键
删除

CREATE   TABLE   [ dbo ] . [ TABLE1 ]  (
     [ UserId ]   [ int ]   IDENTITY  ( 1 ,  1 )  NOT   NULL  ,
     [ name ]   [ char ]  ( 10 ) COLLATE Chinese_PRC_CI_AS  NULL  
)  ON   [ PRIMARY ]


CREATE   TABLE   [ dbo ] . [ TABLE2 ]  (
     [ id ]   [ int ]   IDENTITY  ( 1 ,  1 )  NOT   NULL  ,
     [ Userid ]   [ int ]   NULL  ,
     [ name ]   [ char ]  ( 10 ) COLLATE Chinese_PRC_CI_AS  NULL  
)  ON   [ PRIMARY ]
GO




ALTER   TABLE   [ dbo ] . [ TABLE2 ]   ADD  
     CONSTRAINT   [ FK_TABLE2_TABLE1 ]   FOREIGN   KEY  
    (
         [ Userid ]
    )  REFERENCES   [ dbo ] . [ TABLE1 ]  (
         [ UserId ]
    )  ON   DELETE   CASCADE  
GO

select   *   from  table1

select   *   from  table2

insert  table1  values ( ' def ' )
insert  table2  values  ( 5 , ' def ' )
  select   *   from  table1

select   *   from  table2

delete   from  table1  where  userid = 5