在插入行的事件上更新相同的行。 [英] Updating same row on event of Inserting row.

问题描述

亲爱的，



我必须在表格中插入行时更新一列表格。



场景喜欢：



表名：学生成绩



专栏：student_id，marks，状态



状态的默认值是'通过'



我想查看每个插入事件表格如果标记列值小于35，那么更新表格列的值会失败。



以上场景是为了解问题而设计的。在项目中，我必须使用项目表这样做。



我尝试了触发但发生以下错误：

ORA-04091：表table_name正在变异，触发器/函数可能看不到它



我尝试从触发器调用一个程序仍然是同样的错误仍然请告诉我将使用哪种方法。 />


提前谢谢。

Dear all,

I have to update one column of table on condition while inserting row in table.

Scenario Like:

Table Name : Student Marks

Column : student_id, marks, status

Default value of status is 'Pass'

I want to check on each insert event on table that if marks column value is less than 35 then update table column with value fail.

Above scenario is designed for question understanding. In project I have to do like this with project table.

I have tried trigger but following error occur:
ORA-04091: table table_name is mutating, trigger/function may not see it

I tried by calling one procedure from trigger still same error remains please tell me which approach I will use.

Thanks in advance.

推荐答案

如果你使用存储过程进行插入，你可以这样做：

If you use a stored procedure for inserting you could do:

INSERT INTO StudentMarks
(.other columns..status)
VALUES
(.other parameters..CASE WHEN p_marks < 35 THEN 'FAIL' ELSE 'PASS' END )

假设p_MARKS是存储过程的IN参数之一。实际上你可以用它来干掉SP，但是这样可以更容易地测试和纠正然后在你的代码中使用你的SQL



如果这有帮助，请花点时间接受解决方案，以便其他人可以找到它。谢谢

Assuming p_MARKS is one of the IN parameters for stored procedure. Actually you could do it withous SP, but this makes it easier to test and correct then having your SQL in the code

If this helps, please take time to accept the solution so that others may find it. Thank you

从这个例子中你可能会有所了解...

From this example you may get idea ...

DECLARE @Id INT
DECLARE @Marks INT

SET @Id =1
SET @Marks =70

DECLARE @TempTable TABLE (Student_Id INT,Marks INT,status NVARCHAR(10))

INSERT INTO @TempTable  (Student_Id,Marks,status) VALUES (@Id,@Marks,(CASE WHEN 35<@Marks THEN 'PASS' ELSE 'FAIL' END ))

SET @Id =2
SET @Marks =20


INSERT INTO @TempTable  (Student_Id,Marks,status) VALUES (@Id,@Marks,(CASE WHEN 35<@Marks THEN 'PASS' ELSE 'FAIL' END ))

SELECT * FROM  @TempTable

输出：



Student_Id标记状态
1 70 PASS

2 20 FAIL

Out put:

Student_Id Marks status
1 70 PASS
2 20 FAIL

这篇关于在插入行的事件上更新相同的行。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！