Thoughts about skiping over triggers

[mercwrought]

Hi all. I have a problem and solution and I wanted everyone’s opinion on it.

My problem I writing a sp to merger accounts.
It combined basically everything.

In testing I have discovered that in some cases there is only one row for tables that have triggers that cascade across tables that I update later in the sp. All of these triggers are set to ignore inserts/updates with more than one row.

With all that said I have created what I am calling identity rows in the tables with this problem and in cases in which I have only 1 row to update/insert I am including this identity row so that I bypass the triggers.

Can you think of any problems that I may encounter?
Any suggestions?


Here is what it looks like

create table #s
(
idnumber 	int,
date 		datetime,
timed	        decimal(9,4),
pt		int,
cn		decimal(13,2),
ct		decimal(13,2),
jt		decimal(13,2),
tn		decimal(13,2),
cs		decimal(13,2),
rht	        int
)

-- get sums per day
insert into #s
select @idnumberto,date,sum(timed),sum(pt),sum(cn),sum(ct),sum(jt),sum(tn),sum(cs),sum(rt)
from daily
where idnumber in  (@idnumberto,@idnumberfrom)
group by date
order by date
[red]
if (select count(*) from #s) =1 --  get past the trigger
    BEGIN
    insert into #s select * from daily where idnumber = 0
    delete from daily where idnumber = 0
     END
[/red]
delete from daily where idnumber in  (@idnumberto,@idnumberfrom)
insert into daily select * from #s

 

[mrdenny]

Why not simply disable the trigger before inserting the data, then reenable the trigger.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[mercwrought]

I considered that but I have an interface that is constantly updating and inserting in to tables and I am not sure how that would affect its operations.

 

[mrdenny]

If you do it all within a single transaction other operations should be blocked until the transaction is complete.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)