sql server2005 触发器例子

引用 session和cookie机制 sql server 2005触发器例子2
sql server2005 触发器例子

数据库 2009-10-26 15:36:00 阅读650 评论0   字号：大中小 订阅
===============================
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [TR_Lz_Week_BaseOil_Insert] ON [dbo].[Lz_Week_BaseOil]
FOR INSERT,UPDATE
AS

SET NOCOUNT ON
if update(yy) or Update(wk)

Begin
  update [lzlt].[dbo].[Lz_Week_BaseOil] set
  index_id=cast(I.yy as varchar) + right(cast(power(10,2) as varchar)+I.wk,2) + right(cast(power(10,9) as varchar)+I.id,9)
  from [lzlt].[dbo].[Lz_Week_BaseOil] P
  inner join Inserted I On P.Id = I.ID

Update [lzlt].[dbo].[Lz_Week_BaseOil]
Set  change_rate = P.price -
  (select top 1 price
   from [lzlt].[dbo].[Lz_Week_BaseOil] a
   where a.index_id<=P.index_id and a.lz_BaseOil_product_id=I.lz_BaseOil_product_id
   And a.ID<> I.ID
   Order by a.index_id Desc
  )
from [lzlt].[dbo].[Lz_Week_BaseOil] P
INNER JOIN Inserted AS I ON P.id=I.ID

--剔除错误数据
Update [lzlt].[dbo].[Lz_Week_BaseOil]
Set Change_rate=NULL
from [lzlt].[dbo].[Lz_Week_BaseOil] P
INNER JOIN Inserted AS I ON P.id=I.ID
Where (P.price + P.change_rate < 1) AND (P.price + P.change_rate > -1)

End
SET NOCOUNT OFF
/*
Update [lzlt].[dbo].[Lz_Week_BaseOil]
Set Change_rate=NULL
 
Where (price + change_rate < 1) AND (price + change_rate > -1)
*/


**************************************

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER  [TR_djjz_Update] ON [dbo].[djjz]
FOR Update
AS

SET NOCOUNT ON
Begin

If Update (wp_ggxh)
Begin
  Update [lzlt].[dbo].[djjzList]
  Set
  wp_ggxh = I.wp_ggxh
  from [lzlt].[dbo].[djjzList] P
  inner join Inserted I on I.[sid] = P.[sid]
End

End
SET NOCOUNT OFF

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [TR_djjz_deleted] ON [dbo].[djjz]
FOR delete
AS

SET NOCOUNT ON
Begin
delete from djjzList
Where sid in (select sid from deleted)

Update [lzlt].[dbo].[lz_Domestic_exfactory_product]
Set
isdjjz = 0
from [lzlt].[dbo].[lz_Domestic_exfactory_product] P
inner join deleted I on I.[product_id] = P.[product_id]
And P.[product_Model_Id]=I.[model_id]
/*
Update [lzlt].[dbo].[lz_DomesticMarketProduct]
Set
isdjjz = 0
from [lzlt].[dbo].[lz_DomesticMarketProduct] P
inner join deleted I on I.[product_id] = P.[product_id]
And P.[product_Model_Id]=I.[model_id]
*/
Update [lzlt].[dbo].[lz_International_market_product]
Set
isdjjz = 0
from [lzlt].[dbo].[lz_International_market_product] P
inner join deleted I on I.[product_id] = P.[product_id]
-- And P.[product_Model_Id]=I.[model_id]
/*
Update [lzlt].[dbo].[lz_DomesticMarketOilProduct]
Set
isdjjz = 0
from [lzlt].[dbo].[lz_DomesticMarketOilProduct] P
inner join deleted I on I.[product_id] = P.[product_id]
And P.[ProductModelID]=I.[model_id]
*/

End
SET NOCOUNT OFF

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [TR_djjzList_Insert] ON [dbo].[djjzList]
FOR INSERT,UPDATE
AS

SET NOCOUNT ON
if update(pricedate)

Begin
  update [lzlt].[dbo].[djjzList] set
  index_id=CONVERT(varchar(8) ,I.pricedate, 112) + Replace(CONVERT(varchar(20) ,getdate(), 108) ,':' ,'') +  right(cast(power(10,9) as varchar)+I.id,9)
  from [lzlt].[dbo].[djjzList] P
  inner join Inserted I On P.Id = I.ID

End
SET NOCOUNT OFF

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [qq_delete_bopPrice]
   ON  [dbo].[lz_BaseOil_Price]
   FOR DELETE
AS
BEGIN
DECLARE @product_ID INT
DECLARE @price_date datetime

DECLARE @p_price_date datetime
DECLARE @p_price float

DECLARE @p_Change_Rate float

SET NOCOUNT ON;
/*
SET @price_date = (SELECT price_date FROM Deleted)
SET @product_ID = (SELECT lz_BaseOil_product_id FROM Deleted)

DECLARE @last_date datetime
set @last_date = ( select last_date from lz_BaseOil_product where lz_BaseOil_product_id=@product_ID)
If DateDiff(day,@last_date,@price_date)=0
Begin
  --查找相关数据进入游标  
  DECLARE TR_DELETE_lz_Domestic_exfactory_price_cursor CURSOR FOR
  SELECT TOP 1 price_date,price,Change_Rate FROM lz_BaseOil_Price
  WHERE lz_BaseOil_product_id =@product_id
  order by price_date desc
  OPEN TR_DELETE_lz_Domestic_exfactory_price_cursor

  FETCH NEXT FROM TR_DELETE_lz_Domestic_exfactory_price_cursor
  INTO @p_price_date,@p_price,@p_Change_Rate

  CLOSE TR_DELETE_lz_Domestic_exfactory_price_cursor
  DEALLOCATE TR_DELETE_lz_Domestic_exfactory_price_cursor
  --结束游标
  update lz_BaseOil_product set last_date=@p_price_date,last_price=@p_price, Last_change_Rate=@p_Change_Rate  where lz_BaseOil_product_id=@product_id
End
  --print @next_id
*/
Update lz_BaseOil_product set
last_date=(select top 1 price_date from lz_BaseOil_Price WHERE lz_BaseOil_product_id =I.lz_BaseOil_product_id and lz_BaseOil_Price_Id not in (select lz_BaseOil_Price_Id from Deleted ) order by price_date desc ),
last_price=(select top 1 price from lz_BaseOil_Price WHERE lz_BaseOil_product_id =I.lz_BaseOil_product_id and lz_BaseOil_Price_Id not in(select lz_BaseOil_Price_Id from deleted) order by price_date desc ),
Last_change_Rate=(select top 1 Change_Rate from lz_BaseOil_Price WHERE lz_BaseOil_product_id =I.lz_BaseOil_product_id and lz_BaseOil_Price_Id not in(select lz_BaseOil_Price_Id from deleted) order by price_date desc )
from lz_BaseOil_product P inner join Deleted I
ON P.lz_BaseOil_product_id = I.lz_BaseOil_product_id

SET NOCOUNT OFF;
END


\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go






ALTER TRIGGER [qq_insert_update_bop]
ON  [dbo].[lz_BaseOil_Price]
FOR INSERT,UPDATE
AS
DECLARE @current_date datetime --当前价格日期
DECLARE @product_id int --当前产品id
DECLARE @id int
DECLARE @next_id int
DECLARE @current_price decimal(18, 2) --当前价格
DECLARE @Previous_PRICE decimal(18, 2) --以前一个价格
DECLARE @memo nvarchar(500)
SET NOCOUNT ON
if update(price_date) or update(price)
Begin
  SET @id = (SELECT lz_BaseOil_Price_Id FROM Inserted)
  SET @current_date = (SELECT price_date FROM Inserted)
  SET @product_id = (SELECT lz_BaseOil_product_id FROM Inserted)
  SET @current_price = (SELECT  price FROM Inserted)
  SET @memo = (SELECT memo FROM Inserted)
  SET @Previous_PRICE = ( SELECT TOP 1  price FROM lz_baseOil_Price WHERE lz_BaseOil_product_id=@product_id and  DATEDIFF(day,price_date,@current_date)>0 order by price_date desc )

  SET @Previous_PRICE = isnull(@Previous_PRICE,@current_price)

  update lz_baseOil_Price set
  index_id=CONVERT(varchar(8) ,price_date, 112) + Replace(CONVERT(varchar(20) ,getdate(), 108) ,':' ,'') + right(cast(power(10,9) as varchar)+lz_BaseOil_Price_Id,9)
  ,change_Rate=@current_price-@Previous_PRICE where lz_BaseOil_Price_Id=@id

  set @next_id = ( SELECT TOP 1 lz_BaseOil_Price_Id FROM lz_baseOil_Price WHERE lz_BaseOil_product_id =@product_id and  DATEDIFF(day,price_date,@current_date)<0 order by price_date asc )
  
  if @next_id is not null
   Begin
    update lz_baseOil_Price set yesterday_price=@current_price,change_Rate=price-@current_price where lz_BaseOil_Price_Id=@next_id
   end
  DECLARE @last_date datetime
  set @last_date = ( select last_date from lz_BaseOil_product where lz_BaseOil_product_id=@product_id)
  If DateDiff(day,@last_date,@current_date)>=0
   Begin
    update lz_BaseOil_product set memo=@memo,last_date=@current_date,last_price=@current_price, Last_change_Rate=@current_price-@Previous_PRICE,modify_date=getdate() where lz_BaseOil_product_id=@product_id
   End
  Else
   Begin
    update lz_BaseOil_product set modify_date=getdate() where lz_BaseOil_product_id=@product_id
   End
  --print @next_id
End
SET NOCOUNT OFF




\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER TRIGGER [TR_DELETE_LZ_City] ON [dbo].[Lz_City]
FOR DELETE
AS
BEGIN
DECLARE @CityID INT
DECLARE @ParentID int

SET NOCOUNT ON;

SET @CityID = (SELECT CityID FROM Deleted)
SET @ParentID = (SELECT ParentID FROM Deleted)

If @ParentID > 0
Begin
  update [dbo].[LZ_City] set Child = Child - 1 Where CityID in (select CityID from [dbo].[GetLz_City_Parent](@ParentID))
End
SET NOCOUNT OFF;
END


\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [TR_UPDATE_LZ_City] ON [dbo].[Lz_City]
FOR UPDATE
AS
BEGIN
DECLARE @CityID int
DECLARE @ParentID int


DECLARE @f_CityID INT
DECLARE @f_ParentID int
DECLARE @f_ParentStr nvarchar(250)
DECLARE @f_Depth int
DECLARE @f_RootID int
DECLARE @f_Child int
DECLARE @f_orders int
DECLARE @f_ClassStr nvarchar(250)

SET NOCOUNT ON;

If Update(ParentID)
Begin
  DECLARE @ParentStr nvarchar(250)
  DECLARE @ClassStr nvarchar(250)
  DECLARE @OldParentStr nvarchar(250)
  DECLARE @OldParentID int
  DECLARE @OldDepth int
  DECLARE @Depth int
  DECLARE @Child int
  DECLARE @orders int
  DECLARE @RootID int
  SET @CityID = (SELECT CityID FROM Inserted)
  SET @ParentID = (SELECT ParentID FROM Inserted)
  SET @OldParentID = (SELECT OldParentID FROM Inserted)
  SET @OldParentStr = (SELECT ParentStr FROM Inserted)
  SET @OldDepth = (SELECT Depth FROM Inserted)
  SET @Child = (SELECT Child FROM Inserted)
  If @OldParentID<>@ParentID  --如果改变得了父ID
   Begin

    If @ParentID = 0
     Begin
      SET @RootID = @CityID
      SET @Depth = 0
      --set @ParentStr = ','+ CONVERT(varchar(10) ,@CityID) +','
      set @ParentStr = ''
      SET @orders = (SELECT IsNULL(max(orders),0) FROM [dbo].[LZ_City] where ParentID=@ParentID )
      Update [dbo].[LZ_City] SET OldParentID=@ParentID,ParentStr=',0,',Depth=0,RootID=@CityID,orders=@orders+1,classstr=','+CONVERT(varchar(10) ,@CityID)+','
      WHERE CityID=@CityID
     End
    Else
     Begin
      --@CityID,@ParentID,@ParentStr,@Depth,@RootID,@Child,@orders,@ClassStr

      --查找相关数据进入游标  
      DECLARE TR_INSERT_LZ_City_cursor CURSOR FOR
      SELECT Top 1 CityID,ParentID,ParentStr,Depth,RootID,Child,orders,ClassStr FROM [dbo].[LZ_City]
      WHERE CityID = @ParentID
      OPEN TR_INSERT_LZ_City_cursor

      FETCH NEXT FROM TR_INSERT_LZ_City_cursor
      INTO @f_CityID,@f_ParentID,@f_ParentStr,@f_Depth,@f_RootID,@f_Child,@f_orders,@f_ClassStr

      CLOSE TR_INSERT_LZ_City_cursor
      DEALLOCATE TR_INSERT_LZ_City_cursor
      --结束游标


      If @f_ParentStr=',0,'
       Begin
        set @ParentStr = ','+ CONVERT(varchar(10) ,@f_CityID) +','
       End
      Else
       Begin
        set @ParentStr =  @f_ParentStr + CONVERT(varchar(10) ,@f_CityID) + ','
       End
      SET @RootID = @f_RootID
      SET @Depth = @f_Depth+1
      SET @orders = (SELECT IsNULL(max(orders),0) FROM [dbo].[LZ_City] where ParentID=@ParentID )

      Update [dbo].[LZ_City] SET
      OldParentID=@ParentID,ParentStr=@ParentStr,Depth=@f_Depth+1,RootID=@f_RootID,orders=@orders+1,classstr=@f_ClassStr+CONVERT(varchar(10) ,@CityID)+','
      WHERE CityID=@CityID

     -- update [dbo].[LZ_City] set Child = Child + 1 Where CityID in (select CityID from [dbo].[GetLz_City_Parent](@ParentID))

     End

     If @Child > 0 --如果有子节点
      Begin
       If  @OldParentID = 0
        Begin
         Update [dbo].[LZ_City] set
         Depth=Depth + @Depth-@oldDepth,RootID=@RootID
         ,ParentStr=SUBSTRING(@ParentStr,1,len(@ParentStr)-1)+ParentStr
         ,classstr=SUBSTRING(@ParentStr,1,len(@ParentStr)-1)+classstr
         Where CityID in (select CityID from [dbo].[GetLz_City_GetChildren](@CityID))
        End
       Else
        Begin
         If @ParentID = 0
          Begin
           Update [dbo].[LZ_City] set
           Depth=Depth + @Depth-@oldDepth,RootID=@RootID
           ,ParentStr=','+replace(ParentStr,@oldParentStr,@ParentStr)
           ,classstr=','+replace(classstr,@oldParentStr,@ParentStr)
           Where CityID in (select CityID from [dbo].[GetLz_City_GetChildren](@CityID))
          End
         else
          Begin
           Update [dbo].[LZ_City] set
           Depth=Depth + @Depth-@oldDepth,RootID=@RootID
           ,ParentStr=replace(ParentStr,@oldParentStr,@ParentStr)
           ,classstr=replace(classstr,@oldParentStr,@ParentStr)
           Where CityID in (select CityID from [dbo].[GetLz_City_GetChildren](@CityID))
          end
        End
       update [dbo].[LZ_City] set Child = Child + @Child + 1 Where CityID in (select CityID from [dbo].[GetLz_City_Parent](@ParentID))
      End
     Else
      Begin
       update [dbo].[LZ_City] set Child = Child + 1 Where CityID in (select CityID from [dbo].[GetLz_City_Parent](@ParentID))
      end


     If  @OldParentID > 0
      Begin
       If @Child > 0 --如果有子节点
        Begin
         update [dbo].[LZ_City] set Child = Child - @Child -1 Where CityID in (select CityID from [dbo].[GetLz_City_Parent](@oldParentID))
        End
       Else
        Begin
         update [dbo].[LZ_City] set Child = Child - 1 Where CityID in (select CityID from [dbo].[GetLz_City_Parent](@oldParentID))
        End
      End
   end
End



If Update(AreaID)
Begin
  --区域更新
  update Lz_City set ProvId = NULL,CapiID = NULL,TownID = NULL ,AreaName=D.CityName,ProvName = NULL,CapiName = NULL,TownName = NULL
  FROM Lz_City
  INNER JOIN Inserted AS I ON Lz_City.CityId = I.CityId And Lz_City.Depth = 0
  INNER JOIN Lz_City AS D on I.AreaID = D.CityID
  -- 省份更新
 
  update Lz_City set ProvId = I.CityId,CapiID = NULL,TownID = NULL ,AreaName=D.CityName,ProvName = I.CityName,CapiName = NULL,TownName = NULL
  FROM Lz_City
  INNER JOIN Inserted AS I ON Lz_City.CityId = I.CityId And Lz_City.Depth = 1
  INNER JOIN Lz_City AS D on I.AreaID = D.CityID
 
  -- 市级更新
  update Lz_City set ProvId = I.ParentID,CapiID = I.CityID,TownID = NULL,AreaName=D.CityName,ProvName = B.CityName,CapiName = I.CityName,TownName = NULL
  From Lz_City
  INNER JOIN Inserted AS I ON Lz_City.CityId = I.CityId And Lz_City.Depth = 2
  INNER JOIN Lz_City AS B on Lz_City.ParentId = B.CityID
  INNER JOIN Lz_City AS D on I.AreaID = D.CityID
 
 
 
  -- 县级更新
  update Lz_City set ProvId = B.ParentId,CapiID = I.ParentID,TownID = I.CityID,AreaName=D.CityName,ProvName = C.CityName,CapiName = B.CityName,TownName = I.CityName
  From Lz_City
  INNER JOIN Inserted AS I ON Lz_City.CityId = I.CityId And Lz_City.Depth = 3
  INNER JOIN Lz_City AS B on Lz_City.ParentId = B.CityID
  INNER JOIN Lz_City AS C on B.ParentId = C.CityID
  INNER JOIN Lz_City AS D on I.AreaID = D.CityID

End
SET NOCOUNT OFF
END

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [TR_INSERT_LZ_City] ON [dbo].[Lz_City]
FOR INSERT
AS
DECLARE @CityID INT
DECLARE @ParentID int
DECLARE @ParentStr nvarchar(250)
DECLARE @orders int

DECLARE @f_CityID INT
DECLARE @f_ParentID int
DECLARE @f_ParentStr nvarchar(250)
DECLARE @f_Depth int
DECLARE @f_RootID int
DECLARE @f_AreaID int
DECLARE @f_Child int
DECLARE @f_orders int
DECLARE @f_ClassStr nvarchar(250)
DECLARE @Depth int

SET NOCOUNT ON

SET @CityID = (SELECT CityID FROM Inserted)
SET @ParentID = (SELECT ParentID FROM Inserted)


If @ParentID = 0
Begin
  SET @orders = (SELECT IsNULL(max(orders),0) FROM [dbo].[LZ_City] where ParentID=@ParentID )
  Update [dbo].[LZ_City] SET OldParentID=@ParentID,ParentStr=',0,',Depth=0,RootID=@CityID,AreaID=@CityID,orders=@orders+1,classstr=','+CONVERT(varchar(10) ,@CityID)+','
  WHERE CityID=@CityID
  Set @Depth = 0
End
Else
Begin
  --@CityID,@ParentID,@ParentStr,@Depth,@RootID,@Child,@orders,@ClassStr

  --查找相关数据进入游标  
  DECLARE TR_INSERT_LZ_City_cursor CURSOR FOR
  SELECT Top 1 CityID,ParentID,ParentStr,Depth,RootID,Child,orders,ClassStr,AreaID FROM [dbo].[LZ_City]
  WHERE CityID = @ParentID
  OPEN TR_INSERT_LZ_City_cursor

  FETCH NEXT FROM TR_INSERT_LZ_City_cursor
  INTO @f_CityID,@f_ParentID,@f_ParentStr,@f_Depth,@f_RootID,@f_Child,@f_orders,@f_ClassStr,@f_AreaID

  CLOSE TR_INSERT_LZ_City_cursor
  DEALLOCATE TR_INSERT_LZ_City_cursor
  --结束游标


  If @f_ParentStr=',0,'
   Begin
    set @ParentStr = ','+ CONVERT(varchar(10) ,@f_CityID) +','
   End
  Else
   Begin
    set @ParentStr =  @f_ParentStr + CONVERT(varchar(10) ,@f_CityID) + ','
   End
 
  SET @orders = (SELECT IsNULL(max(orders),0) FROM [dbo].[LZ_City] where ParentID=@ParentID )

  Update [dbo].[LZ_City] SET
  OldParentID=@ParentID,ParentStr=@ParentStr,Depth=@f_Depth+1,RootID=@f_RootID,AreaID=@f_AreaID,orders=@orders+1,classstr=@f_ClassStr+CONVERT(varchar(10) ,@CityID)+','
  WHERE CityID=@CityID
  set @Depth = @f_Depth+1
  update [dbo].[LZ_City] set Child = Child + 1 Where CityID in (select CityID from [dbo].[GetLz_City_Parent](@ParentID))

End

If @Depth=0
Begin

  --区域更新
  update Lz_City set ProvId = NULL,CapiID = NULL,TownID = NULL ,AreaName=D.CityName,ProvName = NULL,CapiName = NULL,TownName = NULL
  FROM Lz_City
  INNER JOIN Inserted AS I ON Lz_City.CityId = I.CityId
  INNER JOIN Lz_City AS D on Lz_City.AreaID = D.CityID
End

If @Depth=1
Begin
  -- 省份更新
 
  update Lz_City set ProvId = I.CityId,CapiID = NULL,TownID = NULL ,AreaName=D.CityName,ProvName = I.CityName,CapiName = NULL,TownName = NULL
  FROM Lz_City
  INNER JOIN Inserted AS I ON Lz_City.CityId = I.CityId
  INNER JOIN Lz_City AS D on Lz_City.AreaID = D.CityID
End
If @Depth=2
Begin
  -- 市级更新
  update Lz_City set ProvId = I.ParentID,CapiID = I.CityID,TownID = NULL,AreaName=D.CityName,ProvName = B.CityName,CapiName = I.CityName,TownName = NULL
  From Lz_City
  INNER JOIN Inserted AS I ON Lz_City.CityId = I.CityId
  INNER JOIN Lz_City AS B on Lz_City.ParentId = B.CityID
  INNER JOIN Lz_City AS D on Lz_City.AreaID = D.CityID
End
 
If @Depth=3
Begin
  -- 县级更新
  update Lz_City set ProvId = B.ParentId,CapiID = I.ParentID,TownID = I.CityID,AreaName=D.CityName,ProvName = C.CityName,CapiName = B.CityName,TownName = I.CityName
  From Lz_City
  INNER JOIN Inserted AS I ON Lz_City.CityId = I.CityId
  INNER JOIN Lz_City AS B on Lz_City.ParentId = B.CityID
  INNER JOIN Lz_City AS C on B.ParentId = C.CityID
  INNER JOIN Lz_City AS D on Lz_City.AreaID = D.CityID
End

SET NOCOUNT OFF

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [TR_DELETE_LZ_Columns] ON [dbo].[LZ_Columns]
FOR DELETE
AS
BEGIN
DECLARE @ColumnID INT
DECLARE @ParentID int

SET NOCOUNT ON;

SET @ColumnID = (SELECT ColumnID FROM Deleted)
SET @ParentID = (SELECT ParentID FROM Deleted)

If @ParentID > 0
Begin
  update [dbo].[LZ_Columns] set Child = Child - 1 Where ColumnID in (select ColumnID from [dbo].[GetLz_Columns_Parent](@ParentID))
End
SET NOCOUNT OFF;
END



\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [TR_lz_Domestic_exfactory_price] ON [dbo].[lz_Domestic_exfactory_price]
FOR INSERT,UPDATE
AS
DECLARE @current_date datetime --当前价格日期
DECLARE @product_id int --当前产品id
DECLARE @id int
DECLARE @next_id int
DECLARE @current_price decimal(18, 2) --当前价格
DECLARE @Previous_PRICE decimal(18, 2) --以前一个价格
DECLARE @memo nvarchar(500)

SET NOCOUNT ON
if update(price_date) or update(exfactory_price)
Begin
  SET @id = (SELECT lz_Domestic_exfactory_price_id FROM Inserted)
  SET @current_date = (SELECT price_date FROM Inserted)
  SET @product_id = (SELECT lz_Domestic_exfactory_product_id FROM Inserted)
  SET @current_price = (SELECT exfactory_price FROM Inserted)
  SET @memo = (SELECT memo FROM Inserted)

  SET @Previous_PRICE = ( SELECT TOP 1 exfactory_price FROM lz_Domestic_exfactory_price WHERE lz_Domestic_exfactory_product_id =@product_id and  DATEDIFF(day,price_date,@current_date)>0 order by price_date desc )

  SET @Previous_PRICE = isnull(@Previous_PRICE,@current_price)
  update lz_Domestic_exfactory_price set
  index_id=CONVERT(varchar(8) ,price_date, 112) + Replace(CONVERT(varchar(20) ,getdate(), 108) ,':' ,'') + right(cast(power(10,9) as varchar)+lz_Domestic_exfactory_price_id,9)
  ,change_Rate=@current_price-@Previous_PRICE
  where lz_Domestic_exfactory_price_id=@id

  set @next_id = ( SELECT TOP 1 lz_Domestic_exfactory_price_id FROM lz_Domestic_exfactory_price WHERE lz_Domestic_exfactory_product_id =@product_id and  DATEDIFF(day,price_date,@current_date)<0 order by price_date asc )
  
  if @next_id is not null
   Begin
    update lz_Domestic_exfactory_price set change_Rate=exfactory_price-@current_price where lz_Domestic_exfactory_price_id=@next_id
   end
  DECLARE @last_date datetime
  set @last_date = ( select last_date from lz_Domestic_exfactory_product where lz_Domestic_exfactory_product_id=@product_id)
  If DateDiff(day,@last_date,@current_date)>=0
   Begin
    update lz_Domestic_exfactory_product set memo=@memo,last_date=@current_date,last_exfactory_price=@current_price, Last_change_Rate=@current_price-@Previous_PRICE,modify_date=getdate() where lz_Domestic_exfactory_product_id=@product_id
   End
  Else
   Begin
    update lz_Domestic_exfactory_product set modify_date=getdate() where lz_Domestic_exfactory_product_id=@product_id
   End
  --print @next_id
End
SET NOCOUNT OFF


\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [TR_DELETE_lz_Domestic_exfactory_price] ON [dbo].[lz_Domestic_exfactory_price]
FOR DELETE
AS
BEGIN
DECLARE @product_ID INT
DECLARE @price_date datetime

DECLARE @p_price_date datetime
DECLARE @p_exfactory_price float
DECLARE @p_Change_Rate int

SET NOCOUNT ON;
/*
SET @price_date = (SELECT price_date FROM Deleted)
SET @product_ID = (SELECT lz_Domestic_exfactory_product_id FROM Deleted)

DECLARE @last_date datetime
set @last_date = ( select last_date from lz_Domestic_exfactory_product where lz_Domestic_exfactory_product_id=@product_ID)
If DateDiff(day,@last_date,@price_date)=0
Begin
  --查找相关数据进入游标  
  DECLARE TR_DELETE_lz_Domestic_exfactory_price_cursor CURSOR FOR
  SELECT TOP 1 price_date,exfactory_price,Change_Rate FROM lz_Domestic_exfactory_price
  WHERE lz_Domestic_exfactory_product_id =@product_id
  order by price_date desc
  OPEN TR_DELETE_lz_Domestic_exfactory_price_cursor

  FETCH NEXT FROM TR_DELETE_lz_Domestic_exfactory_price_cursor
  INTO @p_price_date,@p_exfactory_price,@p_Change_Rate

  CLOSE TR_DELETE_lz_Domestic_exfactory_price_cursor
  DEALLOCATE TR_DELETE_lz_Domestic_exfactory_price_cursor
  --结束游标
  update lz_Domestic_exfactory_product set last_date=@p_price_date,last_exfactory_price=@p_exfactory_price, Last_change_Rate=@p_Change_Rate  where lz_Domestic_exfactory_product_id=@product_id
End
  --print @next_id

*/
Update lz_Domestic_exfactory_product set
last_date=(select top 1 price_date from lz_Domestic_exfactory_price WHERE lz_Domestic_exfactory_product_id =I.lz_Domestic_exfactory_product_id and lz_Domestic_exfactory_price_id not in (select lz_Domestic_exfactory_price_id from Deleted ) order by price_date desc ),
last_exfactory_price=(select top 1 exfactory_price from lz_Domestic_exfactory_price WHERE lz_Domestic_exfactory_product_id =I.lz_Domestic_exfactory_product_id and lz_Domestic_exfactory_price_id not in(select lz_Domestic_exfactory_price_id from deleted) order by price_date desc ),
Last_change_Rate=(select top 1 Change_Rate from lz_Domestic_exfactory_price WHERE lz_Domestic_exfactory_product_id =I.lz_Domestic_exfactory_product_id and lz_Domestic_exfactory_price_id not in(select lz_Domestic_exfactory_price_id from deleted) order by price_date desc )
from lz_Domestic_exfactory_product P inner join Deleted I
ON P.lz_Domestic_exfactory_product_id = I.lz_Domestic_exfactory_product_id

SET NOCOUNT OFF;
END

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [TR_lz_DataSupportMemo_Insert] ON [dbo].[lz_DataSupportMemo]
FOR INSERT,UPDATE
AS

SET NOCOUNT ON
if update(PostDate) Or Update (yy) or Update(MM)
Begin
  update [lzlt].[dbo].[lz_DataSupportMemo] set
  index_id=CONVERT(varchar(8) ,I.PostDate, 112) + cast(I.yy as varchar) + right(cast(power(10,2) as varchar)+I.mm,2) +  right(cast(power(10,9) as varchar)+I.id,9)
  from [lzlt].[dbo].[lz_DataSupportMemo] P
  inner join Inserted I On P.Id = I.ID

End
SET NOCOUNT OFF

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [TR_lz_DataSupport_deleted] ON [dbo].[lz_DataSupport]
FOR delete
AS

SET NOCOUNT ON
Begin
delete from lz_DataSupportMemo
Where sid in (select sid from deleted)

End
SET NOCOUNT OFF


\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [TR_UPDATE_LZ_Columns] ON [dbo].[LZ_Columns]
FOR UPDATE
AS
BEGIN
DECLARE @ColumnID int
DECLARE @ParentID int


DECLARE @f_ColumnID INT
DECLARE @f_ParentID int
DECLARE @f_ParentStr nvarchar(250)
DECLARE @f_Depth int
DECLARE @f_RootID int
DECLARE @f_Child int
DECLARE @f_orders int
DECLARE @f_ClassStr nvarchar(250)

SET NOCOUNT ON;

If Update(ParentID)
Begin
  DECLARE @ParentStr nvarchar(250)
  DECLARE @ClassStr nvarchar(250)
  DECLARE @OldParentStr nvarchar(250)
  DECLARE @OldParentID int
  DECLARE @OldDepth int
  DECLARE @Depth int
  DECLARE @Child int
  DECLARE @orders int
  DECLARE @RootID int
  SET @ColumnID = (SELECT ColumnID FROM Inserted)
  SET @ParentID = (SELECT ParentID FROM Inserted)
  SET @OldParentID = (SELECT OldParentID FROM Inserted)
  SET @OldParentStr = (SELECT ParentStr FROM Inserted)
  SET @OldDepth = (SELECT Depth FROM Inserted)
  SET @Child = (SELECT Child FROM Inserted)
  If @OldParentID<>@ParentID  --如果改变得了父ID
   Begin

    If @ParentID = 0
     Begin
      SET @RootID = @ColumnID
      SET @Depth = 0
      --set @ParentStr = ','+ CONVERT(varchar(10) ,@ColumnID) +','
      set @ParentStr = ''
      SET @orders = (SELECT IsNULL(max(orders),0) FROM [dbo].[LZ_Columns] where ParentID=@ParentID )
      Update [dbo].[LZ_Columns] SET OldParentID=@ParentID,ParentStr=',0,',Depth=0,RootID=@ColumnID,orders=@orders+1,classstr=','+CONVERT(varchar(10) ,@ColumnID)+','
      WHERE ColumnID=@ColumnID
     End
    Else
     Begin
      --@ColumnID,@ParentID,@ParentStr,@Depth,@RootID,@Child,@orders,@ClassStr

      --查找相关数据进入游标  
      DECLARE TR_INSERT_LZ_Columns_cursor CURSOR FOR
      SELECT Top 1 ColumnID,ParentID,ParentStr,Depth,RootID,Child,orders,ClassStr FROM [dbo].[LZ_Columns]
      WHERE ColumnID = @ParentID
      OPEN TR_INSERT_LZ_Columns_cursor

      FETCH NEXT FROM TR_INSERT_LZ_Columns_cursor
      INTO @f_ColumnID,@f_ParentID,@f_ParentStr,@f_Depth,@f_RootID,@f_Child,@f_orders,@f_ClassStr

      CLOSE TR_INSERT_LZ_Columns_cursor
      DEALLOCATE TR_INSERT_LZ_Columns_cursor
      --结束游标


      If @f_ParentStr=',0,'
       Begin
        set @ParentStr = ','+ CONVERT(varchar(10) ,@f_ColumnID) +','
       End
      Else
       Begin
        set @ParentStr =  @f_ParentStr + CONVERT(varchar(10) ,@f_ColumnID) + ','
       End
      SET @RootID = @f_RootID
      SET @Depth = @f_Depth+1
      SET @orders = (SELECT IsNULL(max(orders),0) FROM [dbo].[LZ_Columns] where ParentID=@ParentID )

      Update [dbo].[LZ_Columns] SET
      OldParentID=@ParentID,ParentStr=@ParentStr,Depth=@f_Depth+1,RootID=@f_RootID,orders=@orders+1,classstr=@f_ClassStr+CONVERT(varchar(10) ,@ColumnID)+','
      WHERE ColumnID=@ColumnID

     -- update [dbo].[LZ_Columns] set Child = Child + 1 Where ColumnID in (select ColumnID from [dbo].[GetLz_Columns_Parent](@ParentID))

     End

     If @Child > 0 --如果有子节点
      Begin
       If  @OldParentID = 0
        Begin
         Update [dbo].[LZ_Columns] set
         Depth=Depth + @Depth-@oldDepth,RootID=@RootID
         ,ParentStr=SUBSTRING(@ParentStr,1,len(@ParentStr)-1)+ParentStr
         ,classstr=SUBSTRING(@ParentStr,1,len(@ParentStr)-1)+classstr
         Where ColumnID in (select ColumnID from [dbo].[GetLz_Columns_GetChildren](@ColumnID))
        End
       Else
        Begin
         If @ParentID = 0
          Begin
           Update [dbo].[LZ_Columns] set
           Depth=Depth + @Depth-@oldDepth,RootID=@RootID
           ,ParentStr=','+replace(ParentStr,@oldParentStr,@ParentStr)
           ,classstr=','+replace(classstr,@oldParentStr,@ParentStr)
           Where ColumnID in (select ColumnID from [dbo].[GetLz_Columns_GetChildren](@ColumnID))
          End
         else
          Begin
           Update [dbo].[LZ_Columns] set
           Depth=Depth + @Depth-@oldDepth,RootID=@RootID
           ,ParentStr=replace(ParentStr,@oldParentStr,@ParentStr)
           ,classstr=replace(classstr,@oldParentStr,@ParentStr)
           Where ColumnID in (select ColumnID from [dbo].[GetLz_Columns_GetChildren](@ColumnID))
          end
        End
       update [dbo].[LZ_Columns] set Child = Child + @Child + 1 Where ColumnID in (select ColumnID from [dbo].[GetLz_Columns_Parent](@ParentID))
      End
     Else
      Begin
       update [dbo].[LZ_Columns] set Child = Child + 1 Where ColumnID in (select ColumnID from [dbo].[GetLz_Columns_Parent](@ParentID))
      end


     If  @OldParentID > 0
      Begin
       If @Child > 0 --如果有子节点
        Begin
         update [dbo].[LZ_Columns] set Child = Child - @Child -1 Where ColumnID in (select ColumnID from [dbo].[GetLz_Columns_Parent](@oldParentID))
        End
       Else
        Begin
         update [dbo].[LZ_Columns] set Child = Child - 1 Where ColumnID in (select ColumnID from [dbo].[GetLz_Columns_Parent](@oldParentID))
        End
      End
   end
End

SET NOCOUNT OFF
END