SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 tsql insert trigger multi row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kevin_t8t
Starting Member

USA
4 Posts

Posted - 04/28/2013 :  17:57:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3578 Posts

Posted - 04/28/2013 :  18:06:37  Show Profile  Reply with Quote
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
Go to Top of Page

kevin_t8t
Starting Member

USA
4 Posts

Posted - 04/28/2013 :  18:26:34  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/29/2013 :  00:43:18  Show Profile  Reply with Quote
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
Go to Top of Page

kevin_t8t
Starting Member

USA
4 Posts

Posted - 04/29/2013 :  12:39:18  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/30/2013 :  04:11:06  Show Profile  Reply with Quote
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
Go to Top of Page

kevin_t8t
Starting Member

USA
4 Posts

Posted - 04/30/2013 :  15:14:24  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3578 Posts

Posted - 04/30/2013 :  17:17:01  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000