Updating Inserted record using triggerc

Question

I have a table where I need to update a column's value with the Identity of the Inserted record.

Following is the trigger I have written:

CREATE TRIGGER [dbo].[UpdateRecordID]
on [dbo].[Employee]
AFTER INSERT
AS 
BEGIN   
    UPDATE dbo.Employee
    SET RecordID = (SELECT EmployeeID FROM INSERTED ) WHERE EmployeeID= (SELECT EmployeeID FROM INSERTED )
END

The trigger is created successfully but when I Insert a record into the table I get the following error:

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

If I don't put the where condition in the update statement and write the trigger as following:

ALTER TRIGGER [dbo].[UpdateRecordID]
on [dbo].[Employee]
AFTER INSERT
AS 
BEGIN   
    UPDATE dbo.Employee
    SET RecordID = (SELECT EmployeeID FROM INSERTED )
END

I get the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

What is the best practice to Update a record to keep the Primary Key in another column so that I can add more records in the same table and keep them related?

I can write the Update statement in the Stored Procedure where I am inserting the record using @@Identity but I wanted to do the same using a trigger.

I'm a little confused, why do you need the same ID twice in the same table? — twoleggedhorse, Oct 07 '13 at 15:04
I need that so that I can insert another record in the same table and make the relation between the two records based on that same ID. — Chatur, Oct 07 '13 at 15:05
Wouldn't it be better to have a relationship table? You can then have more than one link between records and you can enforce constraints. — twoleggedhorse, Oct 07 '13 at 15:09
But when you insert another record, trigger will fill that record's `RecordID` with that record's `EmployeeID`. How do you plan to have relation between them? — Nenad Zivkovic, Oct 07 '13 at 15:09
@NenadZivkovic you are right but the situation is something that is asking me to keep the identity in 2 columns. Though I can make some more adjustments so I don't need to do this, I just wanted to know if this can be done or not... — Chatur, Oct 07 '13 at 15:14
Have a look at this SQL fiddle: http://sqlfiddle.com/#!3/04fd5/2 — twoleggedhorse, Oct 07 '13 at 15:18
OK several poor rpractices here. Don;t use a subquery for inteh update use a join and under no circumstances shoudl you ever use @@identity in a database that has triggers or you will have data intergrity problems!!!!!! — HLGEM, Oct 07 '13 at 17:22

score 3 · Answer 1 · answered Oct 07 '13 at 15:05

3

You should join your table on INSERTED table as it can contain more rows.

CREATE TRIGGER [dbo].[UpdateRecordID]
on [dbo].[Employee]
AFTER INSERT
AS 
BEGIN   
    UPDATE e
    SET RecordID = i.EmployeeID 
    FROM dbo.Employee e
    INNER JOIN INSERTED i on i.EmployeeID = e.EmployeeID
END

answered Oct 07 '13 at 15:05

Nenad Zivkovic

18,221
6
42
55

1

This will work but I propse that the OP is wanting to do something that is not really necessary - afterall, why keep the same ID in two seperate columns? Surely this is just adding overhead to the server and also keeping redundant data. I suggest that the design of the database may need to be examined rather than rely on a trigger. – twoleggedhorse Oct 07 '13 at 15:07
Thanks for your answer but this give the 1st error I have mentioned in the question. I want to keep the ID in two columns because I need to insert records which can be related to records inside the same table. – Chatur Oct 07 '13 at 15:10
@twoleggedhorse I agree completely. I supposed OP have some other way and logic to later update `RecordID` for specific rows, and this is just to set some initial value. This is just fix of above trigger. – Nenad Zivkovic Oct 07 '13 at 15:11
@Chatur - this query itself could not possibly produce the above mentioned error. It is probably some other `ON UPDATE` trigger that's causing it. Check for them, please. – Nenad Zivkovic Oct 07 '13 at 15:13
@NenadZivkovic That's a good point...will get back on this... I think I have got the answer... – Chatur Oct 07 '13 at 15:19
1

@NenadZivkovic It was the issue you mention in your last comment. Thanks a lot... :) – Chatur Oct 07 '13 at 15:32

score 0 · Answer 2 · edited May 23 '17 at 12:05

0

What is the best practice to Update a record to keep the Primary Key in another column so that I can add more records in the same table and keep them related?

I think you're looking to represent a hierarchy. Normally the column that holds the relation will be null for rows that have no parent, rather than having their own PK copied into that.

An example:

create table Items (
      ID int not null primary key identity(1, 1)
    , Name varchar(50) not null unique
    , ParentID int null references Items (ID)
);
insert into Items (Name, ParentID) select 'Parent', null;
insert into Items (Name, ParentID) select 'Child', ID from Items where Name = 'Parent';

Notice that we don't need triggers and inserting children records is quite easy.

There are also other methods to representing a hierarchy in SQL including SQL Server's own HierarchyID.

As for your error messages, those might indicate that you have some trigger recursion happening. Check your values for nested and recursive triggers.

edited May 23 '17 at 12:05

Community

1
1

answered Oct 07 '13 at 15:11

Tim Lehner

14,813
4
59
76

Yes you are right, but I want to avoid to right OR in the select statement where I need to get all the records including the parent record: SELECT * FROM dbo.Employee WHERE RecordID=160 OR EmployeeID=160 – Chatur Oct 07 '13 at 15:18
@Chatur I suggest that you do not compromise your schema for the sake of less typing. – Tim Lehner Oct 07 '13 at 15:21
Right, I got it, the only solution is to keep the RecordID in the parent record null... Thanks – Chatur Oct 07 '13 at 15:31
1

@Chatur I'm not saying it's the only way to make things work, but I simply don't see the need to use triggers to keep a hierarchy like this. Triggers should be somewhat of a last resort these days, as most relational needs have a more elegant solution in a modern RDBMS. – Tim Lehner Oct 07 '13 at 15:39

Updating Inserted record using triggerc

2 Answers2