tsql insert trigger multi row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

tsql insert trigger multi row

Author

Topic

kevin_t8t
Starting Member

4 Posts

Posted - 2013-04-28 : 17:57:11

The Data base I am working on has the following insert trigger. THe problem is the trigger will not work with a multi row insert. When I try and insert more than one row at a time I get the following message.

Msg 512, Level 16, State 1, Procedure _ti_UD06, Line 8 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. I think the problem is with the following statement it will return more than one value when multiple rows are inserted.

IF(SELECT PROGRESS_RECID FROM inserted ) IS NULL

How can I make this insert trigger work with multiple rows?

There are 2 Columns on the table PROGRESS_RECID and PROGRESS_RECID_IDENT I thin the PROGRESS_RECID_IDENT is an Identry column for SQL. The PROGRESS_RECID_INDENT is used by the PROGRESS Server.

IF an new record is inserted the trigger checks to see if the PROGRESS_RECID is null and will set it to the inserted.Identity

GO
/****** Object: Trigger [dbo].[_ti_UD06] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* Trigger to increment the ROWID field and update
* any case insensitive columns when a record is inserted.
* 2005 rewrote trigger per Progress documentation.
*/
ALTER trigger [dbo].[_ti_UD06] ON [dbo].[UD06] for insert as
begin
if ( select PROGRESS_RECID from inserted) is NULL
begin
update t set PROGRESS_RECID = i.IDENTITYCOL
from UD06 t JOIN INSERTED i ON
t.PROGRESS_RECID_IDENT_ = i.PROGRESS_RECID_IDENT_
select convert (bigint, @@identity)
end

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-28 : 18:06:37

What you want is something like shown below:

ALTER trigger [dbo].[_ti_UD06] ON [dbo].[UD06] for insert as
begin
	if exists ( select * from inserted WHERE PROGRESS_RECID is NULL)
	begin
		update t set PROGRESS_RECID = i.IDENTITYCOL
		from UD06 t JOIN INSERTED i ON
		t.PROGRESS_RECID_IDENT_ = i.PROGRESS_RECID_IDENT_
		WHERE i.PROGRESS_RECID is null
	end
end

kevin_t8t
Starting Member

4 Posts

Posted - 2013-04-28 : 18:26:34

I tried the change below and the PROGRESS_RECID is null after insert. I am not getting the error now, but the PROGRESS_RECID is null

ALTER trigger [dbo].[_ti_UD06] ON [dbo].[UD06] for insert as
begin
if exists ( select * from inserted WHERE PROGRESS_RECID is NULL)
begin
update t set PROGRESS_RECID = i.IDENTITYCOL
from UD06 t JOIN INSERTED i ON
t.PROGRESS_RECID_IDENT_ = i.PROGRESS_RECID_IDENT_
WHERE i.PROGRESS_RECID is null
end
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-29 : 00:43:18

if your attempt is just to set the PROGRESS_RECID with identity column (PROGRESS_RECID_IDENT) value then why do you need a trigger

why not make PROGRESS_RECID a computed column based on the other field?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kevin_t8t
Starting Member

4 Posts

Posted - 2013-04-29 : 12:39:18

I am not exactly sure of the reason for 2 different progress rec ids. I think that the progress_recid is the identity of records created on the progress side. The only time this is null is on records created from sql. If the recid is null then it is set to be the sames as the progress_recid_ident ( which is the sql identity column). I think that the progress database may modify the progress_recid and that is why it isn't set as an identity column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-30 : 04:11:06

still you could make it a computed column isnt it? is there a case where someone will manually input a value for PROGRESS_RECID which is different from PROGRESS_RECID_IDENT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kevin_t8t
Starting Member

4 Posts

Posted - 2013-04-30 : 15:14:24

I think that the PROGRESS_RECID may not alway match the progress_recid_ident. The progress_recid is updated from the progress data server and it could be differenet than the progress_recid_ident

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-30 : 17:17:01

I understand the logic of what you are trying to do; a computed column probably is not the right thing to use if I understood your requirement. Regardless - in your update statement, there are 3 columns that are of interest in the INSERTED table (i.e., rows inserted into the table [dbo].[UD06]) : 1) PROGRESS_RECID, 2) PROGRESS_RECID_IDENT_ and 3) IDENTITYCOL. I am expecting that for the update to work as expected, PROGRESS_RECID is null, PROGRESS_RECID_IDENT_ and IDENTITYCOL are not null. Can you verify that that is true for some of the rows that gets inserted? If PROGRESS_RECID_IDENT_ is also null, then the update in the trigger will not happen.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources