存储过程和触发器

存储过程与触发器实现数据验证

最新推荐文章于 2025-05-20 13:50:45 发布

原创最新推荐文章于 2025-05-20 13:50:45 发布 · 108 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Go

SQL 专栏收录该内容

8 篇文章

订阅专栏

存储过程：

if exists (select [name] from sysobjects where [name]='Validate')
drop proc Validate
go
create proc Validate
as
declare
	@baozhi varchar(50),
	@biaoti varchar(100),
	@riqi varchar(20)
begin
	select @baozhi='齐鲁晚报',@biaoti=标题,@riqi=日期 from chifei1006.[dbo].齐鲁晚报 where 编号=1
	if exists(select * from rawdata.[dbo].rawdata where 报纸名称=@baozhi and 标题=@biaoti and 日期=@riqi)
	begin
		print '重复数据'
	end
	else
	begin
		insert into rawdata.[dbo].rawdata (日期,版次,标题,URL) (select 日期,版次,标题,URL from chifei1006.[dbo].齐鲁晚报  where 编号=1)
	end
end

--exec Validate

触发器：

IF EXISTS (SELECT [NAME] FROM SYSOBJECTS WHERE [NAME]='tt')
DROP TRIGGER tt
GO
CREATE TRIGGER tt
ON rawdata
FOR INSERT
AS
declare
	@baozhi varchar(50),
	@riqi varchar(20),
	@biaoti varchar(100)
BEGIN TRANSACTION
select @baozhi=报纸名称,@riqi=日期,@biaoti=标题 from INSERTED
IF EXISTS (select * from rawdata.[dbo].rawdata where 报纸名称=@baozhi and 标题=@biaoti and 日期=@riqi)
BEGIN
print ('数据重复!')
ROLLBACK
RETURN
END
COMMIT TRANSACTION
GO