触发器代码如下:
实现的功能为:
Light表删除记录
则Room表的LightNum字段值则相应改变CREATE TRIGGER UpdateRoom_LightNum_DelTri ON Light
AFTER DELETE
AS
DECLARE @RoomNo nvarchar(10),@LightNum int,@LightNo nvarchar(10)Select @LightNo = LightNo from DELETED
Select @RoomNo = RoomNo from DELETED Where LightNo = @LightNo
BEGIN
Update Room Set LightNum = LightNum - 1 Where RoomNo = @RoomNo
END删除的存储过程代码如下:
实现功能:
删除记录成功则返回1否则返回0CREATE PROCEDURE DelLightRecord --创建视图
(
@LightNo nvarchar(10)
)
AS
Declare @ReturnValue bit
BEGIN TRANSACTION Del_LightRecord --开始执行事务IF @LightNo NOT IN (SELECT LightNo FROM Light) --判断表里面是否有该条记录
BEGIN ROLLBACK TRANSACTION Del_LightRecord SET @ReturnValue = 0
RETURN @ReturnValue END
ELSE --否则,执行修改另一个表的记录
BEGIN
DELETE FROM Light WHERE LightNo=@LightNo COMMIT TRANSACTION Del_LightRecord --提交事务 SET @ReturnValue = 1
RETURN @ReturnValue END
select @LightNo=@@Identity
GO
现在的问题是:触发器和存储过程好像有冲突!
删除了触发器则存储过程返回值正常,添加了触发器之后,总是会返回0,并继续执行下面的代码!
现在一头雾水。。
高手们,拜托告诉我为什么?
实现的功能为:
Light表删除记录
则Room表的LightNum字段值则相应改变CREATE TRIGGER UpdateRoom_LightNum_DelTri ON Light
AFTER DELETE
AS
DECLARE @RoomNo nvarchar(10),@LightNum int,@LightNo nvarchar(10)Select @LightNo = LightNo from DELETED
Select @RoomNo = RoomNo from DELETED Where LightNo = @LightNo
BEGIN
Update Room Set LightNum = LightNum - 1 Where RoomNo = @RoomNo
END删除的存储过程代码如下:
实现功能:
删除记录成功则返回1否则返回0CREATE PROCEDURE DelLightRecord --创建视图
(
@LightNo nvarchar(10)
)
AS
Declare @ReturnValue bit
BEGIN TRANSACTION Del_LightRecord --开始执行事务IF @LightNo NOT IN (SELECT LightNo FROM Light) --判断表里面是否有该条记录
BEGIN ROLLBACK TRANSACTION Del_LightRecord SET @ReturnValue = 0
RETURN @ReturnValue END
ELSE --否则,执行修改另一个表的记录
BEGIN
DELETE FROM Light WHERE LightNo=@LightNo COMMIT TRANSACTION Del_LightRecord --提交事务 SET @ReturnValue = 1
RETURN @ReturnValue END
select @LightNo=@@Identity
GO
现在的问题是:触发器和存储过程好像有冲突!
删除了触发器则存储过程返回值正常,添加了触发器之后,总是会返回0,并继续执行下面的代码!
现在一头雾水。。
高手们,拜托告诉我为什么?
Select @RoomNo = RoomNo from DELETED Where LightNo = @LightNo
这是?????Select @RoomNo = RoomNo from DELETED这样不对?
CREATE TRIGGER UpdateRoom_LightNum_DelTri ON Light
AFTER DELETE
AS Update Room Set LightNum = LightNum - 1 Where RoomNo = (SELECT
RoomNo FROM DELETE)
AFTER DELETE
AS
UPDATE Room
SET LightNum = R.LightNum - 1
FROM Room R,DELETED d
WHERE R.RoomNo=d.RoomNo一條條刪除時,可改為以上觸發器
AFTER DELETE
AS
BEGIN
Update Room Set LightNum = LightNum - 1
Where RoomNo IN(SELECT RoomNo FROM DELETED)
END--
CREATE PROCEDURE DelLightRecord --创建视图
(
@LightNo nvarchar(10)
)
AS
IF NOT EXISTS(SELECT 1 FROM Light WHERE LightNo=@LightNo) --判断表里面是否有该条记录
RETURN 0
ELSE --否则,执行修改另一个表的记录
BEGIN
DELETE FROM Light WHERE LightNo=@LightNo
RETURN 1
END
AFTER DELETE
AS Update Room Set LightNum = LightNum - 1 Where RoomNo IN (SELECT
RoomNo FROM DELETE)
这里忘改了
if object_id('Light')is not null drop table Light
go
create table Light(LightNo nvarchar(10),roomno nvarchar(10),id int identity)
if object_id('Room')is not null drop table Room
go
create table Room(RoomNo nvarchar(10),LightNum int)insert Light
select 'a01','a' union
select 'a02','a' union
select 'a03','a' union
select 'b01','b' union
select 'b02','b' union
select 'c01','c' insert room
select 'a',3 union all
select 'b',2 union all
select 'c',1
go
CREATE TRIGGER UpdateRoom_LightNum_DelTri ON Light
AFTER DELETE
AS
DECLARE @RoomNo nvarchar(10),@LightNum int,@LightNo nvarchar(10)Select @LightNo = LightNo from DELETED
Select @RoomNo = RoomNo from DELETED Where LightNo = @LightNo
BEGIN
Update Room Set LightNum = LightNum - 1 Where RoomNo = @RoomNo
END
goif object_id('DelLightRecord') is not null drop proc DelLightRecord
go
CREATE PROCEDURE DelLightRecord --创建视图
(
@LightNo nvarchar(10)
)
AS
Declare @ReturnValue bit
BEGIN TRANSACTION Del_LightRecord --开始执行事务IF @LightNo NOT IN (SELECT LightNo FROM Light) --判断表里面是否有该条记录
BEGIN ROLLBACK TRANSACTION Del_LightRecord SET @ReturnValue = 0
RETURN @ReturnValue END
ELSE --否则,执行修改另一个表的记录
BEGIN
DELETE FROM Light WHERE LightNo=@LightNo COMMIT TRANSACTION Del_LightRecord --提交事务 SET @ReturnValue = 1
RETURN @ReturnValue END
select @LightNo=@@Identity
GO--
DECLARE @RC int
DECLARE @LightNo nvarchar(10)
-- 设置参数值
set @LightNo='a01'
EXEC @RC = [master].[dbo].[DelLightRecord] @LightNo
select @rc
select * from room/*(所影响的行数为 6 行)
(所影响的行数为 3 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
-----------
1(所影响的行数为 1 行)RoomNo LightNum
---------- -----------
a 2
b 2
c 1(所影响的行数为 3 行)
*/
完全改成了最简单的代码,。也就是chuifengde
提供的代码,但是还是不行!还是出现相同的错误!
declare @s int
exec @s=DelLightRecord '108'
select @s
if (myCommand.ExecuteNonQuery() == 1)
{
MessageBox.Show("数据添加成功!");
initLightNo();
LoadData();
}
else
{
MessageBox.Show("数据添加失败!");
}我监视了程序,返回值竟然是2!难怪我最后总是执行数据添加失败!可是为什么会返回2呢?
哥们姐们能不能解释一下?
而且你触发器那样写肯定不行的,如果同时删除多条会出错
{
try
{
int flag;
SqlConnection myConnection = G_SqlExecute.GetCon();
SqlCommand myCommand = new SqlCommand("DelLightRecord", myConnection); //指明sql的操作类型是存储过程
myCommand.CommandType = CommandType.StoredProcedure; string tmp = txtLightNo.Text;
//给存储过程添加参数
SqlParameter para1 = new SqlParameter("@LightNo", SqlDbType.NVarChar, tmp.Length);
para1.Value = tmp;
myCommand.Parameters.Add(para1); if (MessageBox.Show("确定要删除该数据吗?", "删除数据", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
flag = myCommand.ExecuteNonQuery();
if (flag == 1)
{
MessageBox.Show("数据删除成功!");
LoadData();
}
else
{
MessageBox.Show("数据删除失败!");
}
}
myConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
http://blog.csdn.net/xys_777/archive/2010/06/21/5684442.aspx
调试存储过程
http://blog.csdn.net/xys_777/archive/2010/06/18/5678190.aspx
例如用ExecuteNonQuery()方法执行create操作
SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=PSDB;Integrated Security=SSPI");string str = "CREATE TABLE aaa ( " +
"[ID] [int] IDENTITY (1, 1) NOT NULL , " +
"[BasicID] [int] NULL ," +
"[AdoptedName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ," +
"[AdoptedSex] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ," +
"[AdoptBirthday] [smalldatetime] NULL ," +
"[AdoptedType] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ," +
"[ApprTime] [smalldatetime] NULL ," +
"[Re] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL " +
") ON [PRIMARY] "; SqlCommand comm = new SqlCommand(str, conn);
int i = 10;
try
{
conn.Open();
i = comm.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
Response.Write(ex.Message);
} Response.Write(i.ToString()); 如果执行成功的话 返回的值为-1,如果数据表已经存在的话返回异常:数据库中已存在名为 'aaa' 的对象。问题已经解决!给分~虽然不是SQL的问题,但是让我意识到有些程序写得过于复杂,其实实现起来很简单!还有一些就是理解错了!比如二楼说的,触发器有问题deleted是结果集怎么可以赋给变量!!!
终究还是学到了点东西!谢谢各位高手!谢谢大哥,谢谢大姐!