广告的触发器

declare @p1 int
set @p1=0
EXEC UP_ADCategory_ADD @p1 output,'最顶部图片广告','首页',300,100,1,250,'一直出现在顶部',1,1,2,0
EXEC UP_ADCategory_ADD @p1 output,'头部图片广告','首页',150,85,1,250,'头部图片广告，在其他页面也会出现',2,1,8,0
EXEC UP_ADCategory_ADD @p1 output,'头部图片文字','首页',100,100,0,200,'集中出现在首页',3,1,12,0
EXEC UP_ADCategory_ADD @p1 output,'分类图片推荐','首页、列表页',100,100,0,200,'集中出现在首页、列表页',4,1,50,0
EXEC UP_ADCategory_ADD @p1 output,'分类文字推荐','首页、列表页',100,100,0,200,'集中出现在首页、列表页',4,1,50,0

 

declare @p2 int
set @p2=0
EXEC UP_ADs_ADD @p2 output,7,1,'爱购银饰批发网 ',12,'2010-01-01','2010-06-11',1000,'images/ad/hh05.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,7,1,'丰禾礼品 ',12,'2010-01-01','2010-06-11',1000,'images/ad/hh06.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,7,1,'百丰日用品 ',12,'2010-01-01','2010-06-11',1000,'images/ad/hh07.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,7,1,'北京投影机网',12,'2010-01-01','2010-06-11',1000,'images/ad/hh08.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,7,1,'中国奶粉市场 ',12,'2010-01-01','2010-06-11',1000,'images/ad/hh09.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,7,1,'工艺品联盟 ',12,'2010-01-01','2010-06-11',1000,'images/ad/hh10.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,7,1,'高考0距离',12,'2010-01-01','2010-06-11',1000,'images/ad/hh12.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,7,1,'你亲亲我吧',12,'2010-01-01','2010-06-11',1000,'images/ad/hh15.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,7,1,'高考信息网',12,'2010-01-01','2010-06-11',1000,'images/ad/hh16.jpg','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,7,1,'高考复习网',12,'2010-01-01','2010-06-11',1000,'images/ad/hh17.jpg','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,7,1,'中国电子商务论坛',12,'2010-01-01','2010-06-11',1000,'images/ad/jdkz_1.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,7,1,'耀文网络',12,'2010-01-01','2010-06-11',1000,'images/ad/jdkz_1.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,7,1,'成人用品',12,'2010-01-01','2010-06-11',1000,'images/ad/jdkz_2.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,7,1,'学海听潮',12,'2010-01-01','2010-06-11',1000,'images/ad/jdkz_3.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,7,1,'925购物导航',12,'2010-01-01','2010-06-11',1000,'images/ad/jdkz_1.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,7,1,'都市精英俱乐部 ',12,'2010-01-01','2010-06-11',1000,'images/ad/jdkz_9.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0

 

EXEC UP_ADs_ADD @p2 output,8,1,'爱购银饰批发网 ',12,'2010-01-01','2010-06-11',1000,'images/ad/hh05.gif','http://',0,'wyj','[e-mail]','13585980568','284684883',1,0
EXEC UP_ADs_ADD @p2 output,8,1,'丰禾礼品 ',12,'2010-01-01','2010-06-11',1000,'images/ad/hh06.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,8,1,'百丰日用品 ',12,'2010-01-01','2010-06-11',1000,'images/ad/hh07.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,8,1,'北京投影机网',12,'2010-01-01','2010-06-11',1000,'images/ad/hh08.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,8,1,'中国奶粉市场 ',12,'2010-01-01','2010-06-11',1000,'images/ad/hh09.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,8,1,'工艺品联盟 ',12,'2010-01-01','2010-06-11',1000,'images/ad/hh10.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,8,1,'高考0距离',12,'2010-01-01','2010-06-11',1000,'images/ad/hh12.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,8,1,'你亲亲我吧',12,'2010-01-01','2010-06-11',1000,'images/ad/hh15.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,8,1,'高考信息网',12,'2010-01-01','2010-06-11',1000,'images/ad/hh16.jpg','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,8,1,'高考复习网',12,'2010-01-01','2010-06-11',1000,'images/ad/hh17.jpg','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,8,1,'中国电子商务论坛',12,'2010-01-01','2010-06-11',1000,'images/ad/jdkz_1.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,8,1,'耀文网络',12,'2010-01-01','2010-06-11',1000,'images/ad/jdkz_1.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,8,1,'成人用品',12,'2010-01-01','2010-06-11',1000,'images/ad/jdkz_2.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,8,1,'学海听潮',12,'2010-01-01','2010-06-11',1000,'images/ad/jdkz_3.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,8,1,'925购物导航',12,'2010-01-01','2010-06-11',1000,'images/ad/jdkz_1.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0
EXEC UP_ADs_ADD @p2 output,8,1,'都市精英俱乐部 ',12,'2010-01-01','2010-06-11',1000,'images/ad/jdkz_9.gif','http://',0,'wyj','[e-mail]','13575970567','274674883',1,0        

select * from adcategory

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UP_ADCategory_ADD]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UP_ADCategory_ADD]
GO
------------------------------------
--用途：增加一条记录
--项目名称：CodematicDemo
--说明：
--时间：2010-6-10 10:39:23
------------------------------------
CREATE PROCEDURE UP_ADCategory_ADD
@ACId int output,
@TypeName nvarchar(100),
@AdPage nvarchar(50),
@AdWidth int,
@AdHeigh int,
@TexOrPic bit,
@TypePrice int,
@MIntroduction nvarchar(200),
@MOrder int,
@IsShow bit,
@mQuantity int,
@mRemain int

 AS
 INSERT INTO [ADCategory](
 [TypeName],[AdPage],[AdWidth],[AdHeigh],[TexOrPic],[TypePrice],[MIntroduction],[MOrder],[IsShow],[mQuantity],[mRemain]
 )VALUES(
 @TypeName,@AdPage,@AdWidth,@AdHeigh,@TexOrPic,@TypePrice,@MIntroduction,@MOrder,@IsShow,@mQuantity,@mRemain
 )
 SET @ACId = @@IDENTITY

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UP_ADCategory_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UP_ADCategory_Update]
GO
------------------------------------
--用途：修改一条记录
--项目名称：CodematicDemo
--说明：
--时间：2010-6-10 11:41:43
------------------------------------
CREATE PROCEDURE UP_ADCategory_Update
@ACId int,
@TypeName nvarchar(100),
@AdPage nvarchar(50),
@AdWidth int,
@AdHeigh int,
@TexOrPic bit,
@TypePrice int,
@MIntroduction nvarchar(200),
@MOrder int,
@IsShow bit,
@mQuantity int,
@mRemain int
 AS
 UPDATE [ADCategory] SET
 [TypeName] = @TypeName,[AdPage] = @AdPage,[AdWidth] = @AdWidth,[AdHeigh] = @AdHeigh,[TexOrPic] = @TexOrPic,[TypePrice] = @TypePrice,[MIntroduction] = @MIntroduction,[MOrder] = @MOrder,[IsShow] = @IsShow,[mQuantity] = @mQuantity,[mRemain] = @mRemain
 WHERE ACId=@ACId

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UP_ADCategory_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UP_ADCategory_Delete]
GO
------------------------------------
--用途：删除一条记录
--项目名称：CodematicDemo
--说明：
--时间：2010-6-10 11:41:43
------------------------------------
CREATE PROCEDURE UP_ADCategory_Delete
@ACId int
 AS
 DELETE [ADCategory]
  WHERE ACId=@ACId

GO

 

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UP_ADs_ADD]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UP_ADs_ADD]
GO
------------------------------------
--用途：增加一条记录
--项目名称：CodematicDemo
--说明：
--时间：2010-6-10 11:46:20
------------------------------------
CREATE PROCEDURE UP_ADs_ADD
@AdId int output,
@ACId int,
@AgentId int,
@AdInfoName nvarchar(50),
@AdMonth int,
@AdStartTime datetime,
@AdEndTime datetime,
@AdTotalPrice int,
@AdImageAdress nvarchar(100),
@AdLinkAdress nvarchar(100),
@AdIsDelete bit,
@MLinkMan nvarchar(60),
@MLinkEmail nvarchar(60),
@MLinkPhone nvarchar(60),
@MLinkQq nvarchar(60),
@AdStatus bit,
@AdClicks int

 AS
 INSERT INTO [ADs](
 [ACId],[AgentId],[AdInfoName],[AdMonth],[AdStartTime],[AdEndTime],[AdTotalPrice],[AdImageAdress],[AdLinkAdress],[AdIsDelete],[MLinkMan],[MLinkEmail],[MLinkPhone],[MLinkQq],[AdStatus],[AdClicks]
 )VALUES(
 @ACId,@AgentId,@AdInfoName,@AdMonth,@AdStartTime,@AdEndTime,@AdTotalPrice,@AdImageAdress,@AdLinkAdress,@AdIsDelete,@MLinkMan,@MLinkEmail,@MLinkPhone,@MLinkQq,@AdStatus,@AdClicks
 )
 SET @AdId = @@IDENTITY

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UP_ADs_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UP_ADs_Update]
GO
------------------------------------
--用途：修改一条记录
--项目名称：CodematicDemo
--说明：
--时间：2010-6-10 11:46:20
------------------------------------
CREATE PROCEDURE UP_ADs_Update
@AdId int,
@ACId int,
@AgentId int,
@AdInfoName nvarchar(50),
@AdMonth int,
@AdStartTime datetime,
@AdEndTime datetime,
@AdTotalPrice int,
@AdImageAdress nvarchar(100),
@AdLinkAdress nvarchar(100),
@AdIsDelete bit,
@MLinkMan nvarchar(60),
@MLinkEmail nvarchar(60),
@MLinkPhone nvarchar(60),
@MLinkQq nvarchar(60),
@AdStatus bit,
@AdClicks int
 AS
 UPDATE [ADs] SET
 [ACId] = @ACId,[AgentId] = @AgentId,[AdInfoName] = @AdInfoName,[AdMonth] = @AdMonth,[AdStartTime] = @AdStartTime,[AdEndTime] = @AdEndTime,[AdTotalPrice] = @AdTotalPrice,[AdImageAdress] = @AdImageAdress,[AdLinkAdress] = @AdLinkAdress,[AdIsDelete] = @AdIsDelete,[MLinkMan] = @MLinkMan,[MLinkEmail] = @MLinkEmail,[MLinkPhone] = @MLinkPhone,[MLinkQq] = @MLinkQq,[AdStatus] = @AdStatus,[AdClicks] = @AdClicks
 WHERE AdId=@AdId

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UP_ADs_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UP_ADs_Delete]
GO
------------------------------------
--用途：删除一条记录
--项目名称：CodematicDemo
--说明：
--时间：2010-6-10 11:46:20
------------------------------------
CREATE PROCEDURE UP_ADs_Delete
@AdId int
 AS
 DELETE [ADs]
  WHERE AdId=@AdId

GO

------------------------------------
--触发器用途：INSERT
--
------------------------------------
CREATE TRIGGER tr_adcategory
ON ADCategory
FOR INSERT
AS
DECLARE @Quantity int;
SELECT @Quantity=inserted.mQuantity from inserted
IF (@Quantity<0)
 BEGIN
    RAISERROR ('成绩的取值必须大于零', 16, 1)
    ROLLBACK TRANSACTION
 END
ELSE
 BEGIN
  UPDATE ADCategory
        SET mRemain= @Quantity
        FROM ADCategory,inserted
        WHERE ADCategory.ACId=inserted.ACId
 END
go

------------------------------------
--用途：测试
/*
declare @p1 int
set @p1=0
EXEC UP_ADCategory_ADD @p1 output,'测试一','首页',100,100,0,250,'dasdfasdfasdfasdf',10,1,9,8
*/
------------------------------------

------------------------------------
--触发器用途：UPDATE
--
------------------------------------
CREATE TRIGGER tr_adcategory_update
ON ADCategory
FOR UPDATE
AS
DECLARE @Quantity int;
SELECT @Quantity=inserted.mQuantity from inserted
DECLARE @ACID int;
SELECT @ACID=inserted.ACID from inserted
DECLARE @Adamount int;
select @Adamount=count(AdId) from [ads] where  ACId=@ACId and AdStatus=1 and AdIsDelete=0

IF (@Quantity<@Adamount)
 BEGIN
    RAISERROR ('数量少于实际广告数', 16, 1)
    ROLLBACK TRANSACTION
 END
ELSE
 BEGIN
  UPDATE ADCategory
        SET mRemain= @Quantity-@Adamount
        FROM ADCategory,inserted
        WHERE ADCategory.ACId=inserted.ACId
 END
go

------------------------------------
--测试
/*
declare @p1 int
set @p1=0
EXEC UP_ADCategory_ADD @p1 output,'测试一','首页',100,100,0,250,'dasdfasdfasdfasdf',10,1,9,8
*/
------------------------------------

------------------------------------
--触发器用途：
--
------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
create TRIGGER [tr_ads_insert]
ON [dbo].[ADs]
FOR INSERT
AS

DECLARE @ACId int
DECLARE @Remain int
DECLARE @AdStatus int
DECLARE @AdIsDelete int

SELECT @ACId=inserted.ACId, @AdStatus=inserted.AdStatus,  @AdIsDelete=inserted.AdIsDelete  from inserted
IF @AdStatus=1 and @AdIsDelete=0
Begin
 select @Remain=mRemain from  ADCategory where ACId=@ACId
 --print @Remain
 IF not exists (select acid from ADCategory where ADCategory.acid=@ACId)
  BEGIN
      RAISERROR ('没有该广告类别', 16, 1)
      ROLLBACK TRANSACTION
  END

 IF (@Remain-1<0)
  BEGIN
     RAISERROR ('没有空余的广告位置', 16, 1)
     ROLLBACK TRANSACTION
  END
 ELSE
  BEGIN
   UPDATE ADCategory
   SET mRemain= mRemain-1
   WHERE ADCategory.ACId=@ACId
 END
end

------------------------------------
--用途：测试
/*
declare @p1 int
set @p1=0
EXEC UP_ADs_ADD @p1 output,3,1,'infoname',12,'2010-01-11','2010-01-11',1000,'http://','http://',1,'wyj','[e-mail]','13575970567','274674883',0,0
select * from adcategory
*/
------------------------------------