解决方案 »
- 请问一个用其他表更新当前某表的update语句的问题
- 大量连续insert 表 sql server 占用大量内存 如何释放?
- 如何和字符串中的分段字符吕相批配?--很简单的一个问题,请大家帮忙
- 刚才看了两篇防止Sql注入攻击的文章,没弄明白什么是“参数化语句”, 也不知道什么是“拒绝包含二进制数据、转义序列和注释字符的输入内容”
- 附加数据库后只读.....
- 求一个sql
- SQL难题
- sql server 2000的表字段中的什么字符到.net中是\n?
- 已有事务订阅的数据库,怎么暂停其连接,以改变READ_COMMITTED_SNAPSHOT
- sql server 2005 远程死活连不上,help me
- 求SQL语句
- SQL Sever 2008 R2无法登录
ON dbo.sc
AFTER INSERT
AS
DECLARE @tatal tinyint
SELECT @tatal=sum(credit)
FROM (
SELECT sno,credit
FROM sc,course
WHERE sc.sno=inserted.sno AND sc.sno=course.sno) A
IF @tatal > 30
BEGIN
ROLLBACK
RAISERROR('错误提示',16,1)
END
GO
ON dbo.sc
AFTER INSERT
AS
DECLARE @tatal tinyint
SELECT @tatal=sum(credit)
FROM (
SELECT sno,credit
FROM sc,course
WHERE sc.sno=inserted.sno AND sc.sno=course.sno)t
IF @tatal > 30
BEGIN
ROLLBACK
RAISERROR('错误提示',16,1)
END
GO
ON dbo.sc
AFTER INSERT
AS
DECLARE @tatal tinyint
SELECT @tatal=sum(credit)
FROM (
SELECT sno,credit
FROM sc,course
WHERE sc.sno=inserted.sno AND sc.sno=course.sno) as T--把一个查询语句作为表的时候对其进行查询需要取别名
IF @tatal > 30
BEGIN
ROLLBACK
RAISERROR('错误提示',16,1)
END
GO
ON dbo.sc
AFTER INSERT
AS
DECLARE @tatal tinyint
SELECT @tatal=sum(credit)
FROM (
SELECT sno,credit
FROM sc,course,inserted
WHERE sc.sno=inserted.sno AND sc.sno=course.sno) A
IF @tatal > 30
BEGIN
ROLLBACK
RAISERROR('错误提示',16,1)
END
GO
CREATE TRIGGER dbo.ck_credit
ON dbo.sc
AFTER INSERT
AS
DECLARE @tatal tinyint
SELECT @tatal=sum(credit)
FROM (
SELECT sno,credit
FROM sc,course
WHERE sc.sno=inserted.sno AND sc.sno=course.sno)a
IF @tatal > 30
BEGIN
ROLLBACK
RAISERROR('错误提示',16,1)
END
GO
感谢!现在又出现这种错误了:
消息 137,级别 15,状态 1,第 1 行
必须声明标量变量 "@tatal"。
变量已经声明了,是不能在查询语句上赋值聚集函数值还是什么原因? 又麻烦了~
CREATE TRIGGER dbo.ck_credit
ON dbo.sc
AFTER INSERT
AS
--DECLARE @tatal tinyint
IF (SELECT sum(credit)
FROM (
SELECT sno,credit
FROM sc,course
WHERE sc.sno=inserted.sno AND sc.sno=course.sno) as T)>30
BEGIN
ROLLBACK
RAISERROR('错误提示',16,1)
END
GO
无法绑定由多个部分组成的标识符 "inserted.sno"。
感觉还是聚集的原因?
CREATE TRIGGER dbo.ck_credit
ON dbo.sc
AFTER INSERT
AS
--DECLARE @tatal tinyint
IF (SELECT sum(credit所在的表名.credit)
FROM (
SELECT sno,credit
FROM sc,course,inserted
WHERE sc.sno=inserted.sno AND sc.sno=course.sno) as T)>30
BEGIN
ROLLBACK
RAISERROR('错误提示',16,1)
END
GO--你自己连查询语句都没写明白
CREATE TRIGGER dbo.ck_credit
ON dbo.sc
AFTER INSERT
AS
--DECLARE @tatal tinyint
--SELECT @tatal=sum(credit)
IF ( SELECT sum(T.credit)
FROM (
SELECT sno,credit
FROM sc,course
WHERE sc.sno=inserted.sno AND
sc.cno=course.cno) AS T) >30
--IF @tatal > 30
BEGIN
ROLLBACK
RAISERROR('错误提示',16,1)
END
GO
[/code]
是想这样子做的,触发后inserted应该只有一个记录吧,刚插入的数据也在sc中吧?credit只在course表中
,通过连接查询那只剩刚插入记录sno的全部credit咯,不过改了还是提示这错误:
消息 4104,级别 16,状态 1,过程 ck_credit,第 8 行
无法绑定由多个部分组成的标识符 "inserted.sno"。
ON dbo.sc
AFTER INSERT
AS
--DECLARE @tatal tinyint
--SELECT @tatal=sum(credit)
IF ( SELECT sum(T.credit)
FROM (
SELECT sno,credit
FROM sc,course,inserted
WHERE sc.sno=inserted.sno AND
sc.cno=course.cno) AS T) >30
--IF @tatal > 30
BEGIN
ROLLBACK
RAISERROR('错误提示',16,1)
END
GO
CREATE TRIGGER dbo.ck_credit
ON dbo.sc
AFTER INSERT
AS
--DECLARE @tatal tinyint
IF (SELECT sum(credit所在的表名.credit)
FROM (
SELECT sno,credit
FROM sc,course,inserted--少了一个表啊,我给你加上你又不看
WHERE sc.sno=inserted.sno AND sc.sno=course.sno) as T)>30
BEGIN
ROLLBACK
RAISERROR('错误提示',16,1)
END
GO