Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2010
    Posts
    12

    Lightbulb Unanswered: SQL Triggers for Multi-Row Operations

    The following trigger works for T-SQL insert statements with only one row, but I want to make it for insert statements with more than one "value" row.

    When I issue the command,
    "insert into vwDream(EntryDateTime,EntryTypeID)
    values ('2010-01-01 01:00','2'"

    it works.

    But when I issue a command with multiple value rows,
    "insert into vwDream(EntryDateTime,EntryTypeID)
    values ('2010-01-01 01:00','2'),('2000-01-12','1')"

    it gives me the 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."

    How do I make the following code work for inserts with multiple rows? Some sample code is below.

    Code:
    create trigger TR_vwDreamCreate
    on vwDream
    instead of insert
    as
    
    begin
    
    if @@ROWCOUNT=0
    return
    
    if((select EntryID from inserted i) is not null)
    begin
    	rollback;
    	print 'debug: error: cannot specify EntryID'
    end
    
    if exists
    (
    	select top 1 * from inserted i where
    	EntryID is null and
    	EntryDateTime is not null
    )
    begin
    	declare @myEntryDateTime as datetime
    	@myEntryDateTime = i.EntryDateTime from inserted i
    
    	if((select EntryDateTime from inserted i) is null)
    	begin
    		print 'error.'
    	end
    	else
    	begin
    		insert into Entry(EntryDateTime,EntryTypeID)
    		select i.EntryDateTime, i.EntryTypeID from inserted i
    	end
    end
    
    end
    I would like to stick with using a trigger; I don't want to use a cursor.
    Last edited by jeff0000; 09-04-10 at 12:57.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try:
    Code:
    create trigger TR_vwDreamCreate
    on vwDream
    instead of insert
    as
    begin
    	if @@ROWCOUNT=0
    		return
    
    	if exists (select 1 from inserted i where i.EntryID IS NOT NULL)
    	begin
    		rollback;
    		print 'debug: error: cannot specify EntryID'
    		return
    	end
    
    	if exists (select 1 from inserted i where EntryDateTime is null)
    	begin
    		rollback;
    		print 'error: EntryDateTime is not (always) specified'
    		return
    	end
    
    	insert into Entry(EntryDateTime, EntryTypeID)
    	select i.EntryDateTime, i.EntryTypeID 
    	from inserted i
    
    end
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jul 2010
    Posts
    12
    Thanks, Wim. Syntactically, the code runs, but I need a way to figure out how to reference values from the same row. The trigger that I'm going to be coding is more complex and involves having to grab values from the same row every time, for each row in the insert statement.

    I started coding more of the trigger, and it's here below.

    Code:
    create trigger TR_vwDreamInsert
    on vwDream
    instead of insert
    as
    
    begin
    
    	if @@ROWCOUNT=0
    		return
    
    	if exists((select 1 from inserted i where i.EntryID is not null))
    	begin
    		rollback;
    		print 'vwDreamInsert: DEBUG: Unable to add DreamSeq records for existing Dream records - not implemented at this time. Aborting...'
    		return
    	end
    
    	if exists (select 1 from inserted i where
    		i.EntryID is null and
    		i.EntryDateTime is not null and
    		i.EntryTypeID is not null and
    		i.DreamSeqUnicode is not null and
    		((i.DreamSeqNText is not null) or (i.DreamSeqUText is not null)))
    	begin
    		print 'vwDreamInsert: DEBUG: Adequate data for Dream / DreamSeq records to be inserted exists. Continuing...'
    		
    		declare @myEntryID as int
    		declare @myDreamID as int
    		declare @myDreamSeqID as int
    		
    		insert into Entry(EntryDateTime,EntryTypeID)
    			select i.EntryDateTime, '2' from inserted i
    			
    		select @myEntryID = (select top 1 EntryID from Entry order by EntryID desc)
    		
    		if exists(select 1 from inserted i where i.DreamUnicode = 'FALSE')
    			insert into Dream(EntryID,EntryTypeID,NTitle,Unicode)
    				select @myEntryID, '2', i.DreamNTitle, i.DreamUnicode from inserted i
    		
    	end
    	else
    	begin
    		print 'vwDreamInsert: DEBUG: ERROR: Adequate data not provided for insert request. Aborting...'
    		rollback
    		return
    	end
    end
    The code that I bolded

    Code:
    select 1 from inserted i where
    		i.EntryID is null and
    		i.EntryDateTime is not null and
    		i.EntryTypeID is not null and
    		i.DreamSeqUnicode is not null and
    		((i.DreamSeqNText is not null) or (i.DreamSeqUText is not null)))
    and

    Code:
    select i.EntryDateTime, '2' from inserted i
    seem to reference possibly different rows, and I need a way to reference the same row. Is there a way to do this? I also need to process this trigger for every row in the insert statement.
    Last edited by jeff0000; 09-05-10 at 08:58.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    It happens a lot recently on this forum:
    Q: Help me with Problem A
    R: Here is solution to Problem A
    Q: Your solution is useless, because my real problem is Problem B.
    R: How was I supposed to know that !?

    Don't expect a useful response on this forum, if you don't tell us your real problem.

    That aside. Can you explain what you really want? The code now goes in the line of:
    If there exists 1 record in the list of inserted records, that meets all my criteria:
    then insert ALL the records, those who meet my criteria as well as those that don't.

    What works wonders is some example data: data that matches your criteria and data that doesn't.
    And the resulting actions, data, ... that you expect for the given example data.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jul 2010
    Posts
    12
    I want to make the following code

    Code:
    create trigger TR_vwDreamInsert
    on vwDream
    instead of insert
    as
    
    begin
    
    	if @@ROWCOUNT=0
    		return
    
    	if exists((select 1 from inserted i where i.EntryID is not null))
    	begin
    		rollback;
    		print 'vwDreamInsert: DEBUG: Unable to add DreamSeq records for existing Dream records - not implemented at this time. Aborting...'
    		return
    	end
    
    	if exists (select 1 from inserted i where
    		i.EntryID is null and
    		i.EntryDateTime is not null and
    		i.EntryTypeID is not null and
    		i.DreamSeqUnicode is not null and
    		((i.DreamSeqNText is not null) or (i.DreamSeqUText is not null)))
    	begin
    		print 'vwDreamInsert: DEBUG: Adequate data for Dream / DreamSeq records to be inserted exists. Continuing...'
    		
    		declare @myEntryID as int
    		declare @myDreamID as int
    		declare @myDreamSeqID as int
    		
    		insert into Entry(EntryDateTime,EntryTypeID)
    			select i.EntryDateTime, '2' from inserted i
    			
    		select @myEntryID = (select top 1 EntryID from Entry order by EntryID desc)
    		
    		if exists(select 1 from inserted i where i.DreamUnicode = 'FALSE')
    			insert into Dream(EntryID,EntryTypeID,NTitle,Unicode)
    				select @myEntryID, '2', i.DreamNTitle, i.DreamUnicode from inserted i
    		
    	end
    	else
    	begin
    		print 'vwDreamInsert: DEBUG: ERROR: Adequate data not provided for insert request. Aborting...'
    		rollback
    		return
    	end
    end
    execute for each row of data in a multi-row insert statement.

    If any row comes up positive for this:
    select 1 from inserted i where i.EntryID is not null,

    I want the insert to fail and to rollback any transactions that may have been inserted already.

    I want the insert statement

    insert into Entry(EntryDateTime,EntryTypeID)
    select i.EntryDateTime, '2' from inserted i

    to insert the data from the from the same row as the following chunk:

    if exists (select 1 from inserted i where
    i.EntryID is null and
    i.EntryDateTime is not null and
    i.EntryTypeID is not null and
    i.DreamSeqUnicode is not null and
    ((i.DreamSeqNText is not null) or (i.DreamSeqUText is not null)))

    Is this possible? If there are multiple rows that fulfill the conditions as the above "if exists" statement, I want the script to process all of them. Thanks for your help, Wim.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Just add those criteria to the WHERE clause of the SELECT statement:
    Code:
    insert into Entry(EntryDateTime,EntryTypeID)
    select i.EntryDateTime, '2' 
    from inserted i
    where	i.EntryID is null and
    	i.EntryDateTime is not null and
    	i.EntryTypeID is not null and
    	i.DreamSeqUnicode is not null and
    	((i.DreamSeqNText is not null) or 
    	(i.DreamSeqUText is not null))
    	)
    I'm not really sure this is what you want.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •