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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','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','wyj@163.com','13575970567','274674883',0,0
select * from adcategory
*/
------------------------------------