sql: TRIGGER

最新推荐文章于 2025-02-11 20:27:37 发布

Msro

最新推荐文章于 2025-02-11 20:27:37 发布

阅读量113

点赞数

本文深入探讨了SQL中常用的高级查询技巧，包括Common Table Expressions (CTE) 的使用，游标的实现方式，以及如何选择特定范围内的记录。此外，还介绍了触发器的创建和使用，以确保数据的完整性和一致性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--Common Table Expressions(CTE)
WITH HighSample (SampleId,SampleTitle,SampleContent) AS
(
    SELECT SampleId,SampleTitle,SampleContent From CompetitionSample
    WHERE SampleId>1
)
SELECT * From HighSample

--游标
DECLARE curSample INSENSITIVE CURSOR
FOR SELECT TOP 10 SampleId,SampleTitle,SampleContent FROM CompetitionSample
DECLARE @SampleId int
DECLARE @SampleTitle nvarchar(540)
Declare @SampleContent nvarchar(1000)
Open curSample
FETCH NEXT FROM curSample INTO @SampleId,@SampleTitle,@SampleContent
WHILE @@Fetch_Status=0
  BEGIN
    PRINT @SampleTitle+','+@SampleContent
    Fetch NEXT FROM curSample Into @SampleId,@SampleTitle,@SampleContent
  END
CLOSE curSample
DEALLOCATE curSample

--N到M条记录(要有主索引ID)
--Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID   Desc
select * from ShoppingCouponsList
Select Top (15-10) * From ShoppingCouponsList Where ShoppingCouponsID in (Select Top 15 ShoppingCouponsID From ShoppingCouponsList) Order by ShoppingCouponsID   Desc


--触发器(添加，修改）
--text,ntext 无法创建约束，可以用此方法
drop TRIGGER Sample_insert

CREATE TRIGGER DuSample_insert
ON CompetitionSample
FOR INSERT,UPDATE AS --常见的触发器有三种：分别应用于Insert , Update , Delete 事件
declare @s nvarchar(500)
set @s='err,geovindu'
IF EXISTS (SELECT * FROM  CompetitionSample  where charindex('</title>',SampleContent)=1)
begin
ROLLBACK TRANSACTION
RAISERROR('有非法字符',16,1,@s)
end


--修改不了
update CompetitionSample set SampleContent='</title>' where SampleId='1'
--添加不了
insert into CompetitionSample(SampleTitle,SampleBrandId,SamplePlatformId,SampleSmallPic,SampleContent,SampleAuthor,SampleIs)
 values('d2',1,1,'gif','</title>2','g2',0)


select * from CompetitionSample where charindex('</title>',SampleContent)=1