Updating same row on event of Inserting row.

Question

4.00/5 (1 vote)

See more:

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.

Posted 24-Sep-14 0:24am

Tejas Sawant 13

Add a Solution

Comments

Sinisa Hajnal 24-Sep-14 7:17am

Why not use stored procedure and checking the length of the parameter before inserting?

George Jonsson 24-Sep-14 7:18am

You need to show your code.
And if this is not homework, you could do it without a trigger when you do the insertion.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sinisa Hajnal · Accepted Answer · 2014-09-24T01:20:00

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

SQL

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

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

Santhosh Babu Mahimairaj · Accepted Answer · 2014-09-25T01:54:00

From this example you may get idea ...

SQL

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

Out put:

Student_Id Marks status
1 70 PASS
2 20 FAIL

Updating same row on event of Inserting row.

2 solutions

Solution 1

Solution 3

Add your solution here

Preview 0