multiple row update

[jgurgen]

i have a table where im trying to update multiple rows based on a date. I was getting an error with my trigger because it was trying to update another table 1 row at a time.

so i created a procedure to look at the inserted table but i get invalid object 'Inserted'

How do i create a procedure that when there is a multiple table insert or update i can update a separate table line by line?

 

[SQLSister]

SOunds like the problem is the trigger is poorly designed and cannot accomodate multiple record inserts.
POst the trigger code if you can and we can look at it to see if it is the problem. Many people who write triggers do not consider that there will be multiple record imports. Or they think erroneousdly that each record will be handled separately by the trigger.

The reason why you cannot see the inserted table is that it only exists inthe context of a trigger running.

If you want to see how the trigger would handle the muliple line insert and run individual steps, what I do (on development only not production) is change the name in the code (minus the create trigger parts of course, this is so you can run the steps in QA) from inserted to a temp table named #inserted. First I create the table #inserted, then I populate it will two or more records that have the kind of data my inserts would have and then run the code and check the results. Once I can get it to run without a problem I change it back to inserted and then put the create trigger code around it and then create the trigger.

Questions about posting. See faq183-874

 

[jgurgen]

TRIGGER

IF(UPDATE(TranType))
BEGIN
	DECLARE @TT1 bit,
	@LoanID_TT varchar(10),
	@HC_TT smallint

	SET @TT1 = (SELECT TranType FROM Inserted)
	SET @LoanID_TT = (SELECT LoanID FROM Inserted)
	SET @HC_TT = (SELECT HistoryCounter FROM Inserted)

	UPDATE iSplit_Disb_Transactions SET TranType=@TT1 WHERE LoanID=@LoanID_TT AND HistoryCounter=@HC_TT

	-- EXEC P_iSplit_Disb_Calc @LoanID_TT
END

STORED PROCEDURE

DECLARE @iLoanID varchar(10),
		@iHC smallint,
		@iTD datetime,
		@iTC smallint,
		@iTA money,
		@iTT bit,
		@iI bit
		
DECLARE iCursor CURSOR
FOR
SELECT LoanID, HistoryCounter, TranDate, TranCode, TranAmt, TranType, Include
FROM Inserted

Open iCursor

FETCH NEXT FROM iCursor INTO (@iLoanID, @iHC, @iTD, @iTC, @iTA, @iTT, @iI)
WHILE @@FETCH_STATUS = 0
BEGIN
	/* For each item we must update the isplit_disb_transactions table */
	/* if include = 0 then delete from transactions table */
	if(@iI=0)
		DELETE iSplit_Disb_Transactions WHERE LoanID = @iLoanID AND HistoryCounter=@iHC
	/* else include = 1 insert into transactions table */
	else
		INSERT INTO iSplit_Disb_Transactions (LoanID, HistoryCounter, TranDate, TranCode, TranAmt, TranType) VALUES (@iLoanID, @iHC, @iTD, @iTC, @iTA, @iTT)
END 

Close iCursor
Deallocate iCursor

 

[SQLSister]

Ok both those are bad.

First never, and I repeat never, use a cursor to do a delete or an insert. Cursors are performance killers and should be avoided at all costs. There is no reason whatsoever to use a cusor in this case. Do a delete with a join to the select statement and use a select statement instead of the values clause in the insert. you can handle the if part inteh where clause. These are basic structures for these kinds of statements and you should look up the syntax in Books online and learn to avoid using cursors. in fact, until you are much more experienced, you should not write another cursor. You will find that if you do not allow yourself to write cursors, that they can be avoided in virtually every case. Learn to do set-based processing before you ever consider using a cursor again.

The same thing with your trigger, this will only give you the value of one of the records. You CANNOT use this syntax in a trigger and expect to maintain data integrity. This syntax in a trigger is a serious error. Triggers must account for multiple record inserts. The update statement has a from clause, use it instead.

Now it appears that what you would like to do is call the stored proc from the trigger? The reason why it doesn't recognize the inserted table is that it has gone out of scope. Why do this in a stored proc anyway? Put the delete and insert statements directly in the trigger.





Questions about posting. See faq183-874

 

[jgurgen]

so if i have two tables
isplit_disb_alltran whcih contains all transactions with an include field
and change to the include field i need to change the
isplit_disb_transactions table

in both tables the primary key is loanid, historycounter

 

[jgurgen]

ok so i did an sql query to get get what doesnt exist now how do i do an insert or delete based upon it?

select a.loanid, a.historycounter, a.trandate, a.trancode, a.tranamt, a.trantype, a.include
from isplit_disb_alltran a
where a.trantype >=0 and not exists (select * from isplit_disb_transactions b where a.loanid=b.loanid and a.historycounter=b.historycounter)

 

[jgurgen]

INSERT INTO iSplit_Disb_Transactions (LoanID, HistoryCounter, TranDate, TranCode, TranAmt, TranType)
SELECT a.loanid, a.historycounter, a.trandate, a.trancode, a.tranamt, a.trantype
FROM isplit_disb_alltran a
WHERE a.include=1 and a.trantype>=0 and not exists (SELECT * FROM isplit_disb_transactions b WHERE a.loanid=b.loanid and a.historycounter=b.historycounter)


is giving me this error but why wont it do a bulk insert?
Server: Msg 512, Level 16, State 1, Procedure TranType_Insert, Line 7
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.

 

[SQLSister]

try a left join instead of a subquery

Questions about posting. See faq183-874