confusion about triggers - 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

SQL Server 2000 Forums

Transact-SQL (2000)

confusion about triggers

Author

Topic

tripathibhupesh
Starting Member

10 Posts

Posted - 2006-04-21 : 14:23:51

hi all
i have a table called processedcdr (accid,cost,country,calltime...)

now what i am trying to do is write a trigger for update on the table now i am new to using trigger n havin some prob

This is my update statement
update processedcdr
set cost=ceiling(duration/60)*.09
where country like 'Mexico%'

now what i have to do is to on update of this table update another table accounts(accountid,credit,debit,balance)

for this i have written a trigger

CREATE TRIGGER onup ON temp
FOR update
AS
declare @cost money
declare @oldcost money
declare @calltime datetime
declare @accountid varchar(25)
declare @calltime_gmt datetime
begin
declare update_acc cursor
for
select i.accountid,datepart(month,i.calltime_gmt),i.calltime_gmt,i.cost,d.cost from inserted i,deleted d where i.accountid=d.accountid
open update_acc
fetch next from update_acc into @accountid,@calltime,@calltime_gmt,@cost,@oldcost
while(@@fetch_status<>-1)
begin
if(@@fetch_status<>-2)
begin
select @accountid
select @cost
select @oldcost
update accounts set debit=(debit+@cost-@oldcost) where accountid=@accountid
update usagehistory set amount=(amount+@cost-@oldcost) where datepart(month,mon)=datepart(month,@calltime_gmt) and type='outbound'
end
fetch next from update_acc into @accountid,@calltime,@calltime_gmt,@cost,@oldcost
end
close update_acc
deallocate update_acc
end

now as far as i understood my trigger will fire for every update statement so the above stmt is a single up stmt fetching for eg 2
rows so what hpns now is my account table gets updated 4 times
if its 3 thn 9 n so on

which shows for the loop works twice
but when i remove the loop i get error stating that the subquery returns more value (while assigning values to the variables)
I am really confused n hence sorry if not up to mark to explain my doubt

please help

Kristen
Test

22859 Posts

Posted - 2006-04-21 : 14:54:30

I've only had a brief look, but do you really need a Cursor? Can;t you just update the accounts and usagehistory tables for all rows in a single statement?

Kristen

tripathibhupesh
Starting Member

10 Posts

Posted - 2006-04-21 : 15:04:12

hi
there would be multiple accountid with respective costs
+ i have to make update in account table in the respective accountids

how cn i do it in a single stmt

don i need to loop for evry accid n sum the cost n then update

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-22 : 02:18:13

Does this helps???


CREATE TRIGGER onup ON temp
	FOR update
	AS
	declare  @cost money 
	declare  @oldcost money 
	declare  @calltime datetime
	declare  @accountid varchar(25)
            declare  @calltime_gmt datetime
 begin 

	  update accounts set debit=(debit+tbl.Cost-tbl.Cost2)  From 
	(select i.accountid,datepart(month,i.calltime_gmt),i.calltime_gmt,i.cost,d.cost As Cost2  from inserted i,deleted d where i.accountid=d.accountid) As Tbl 
	Inner Join Accounts On Tbl.accountid=Accounts.accountid

          update usagehistory set amount=(amount+Tbl.Cost-Tbl2.Cost2) From 
	(select i.accountid,datepart(month,i.calltime_gmt),i.calltime_gmt,i.cost,d.cost As Cost2  from inserted i,deleted d where i.accountid=d.accountid) As Tbl 
	Inner Join UsageHistory On datepart(month,UsageHistory.mon)=datepart(month,Tbl.calltime_gmt) 
	where and UsageHistory.type='outbound'

	
end

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Kristen
Test

22859 Posts

Posted - 2006-04-22 : 05:59:48

Cool! That's exactly the non-cursor sort of solution I was imagining!

Kristen

tripathibhupesh
Starting Member

10 Posts

Posted - 2006-04-27 : 06:53:14

hey chirag thanks for the query
really sorry couldn reply on time
but i guess now i m on the other side of the bay

let m explain in detail

i have a table temp(accid,cost,country,calltime_gmt...)
wherein all de data lies and the summary of it is added to
table accounts(accountid,credit,debit,balance)
Now if in case due to some prob like rate change if the data in temp has to be reprocessed than i want that change to reflect in accounts also
now my change or update query
goes something like this
update temp set cost='.59'where country like'Mexico%'

so now before when i was using cursors it used to update accounts multiple times that is if there r 2 rows than 4 times n so on

now with ur solution iwhat i m facing is completely opposite it just update the accounts with the last record for a given accountid
eg accid country cost
1 Mexico .59
1 Mexico .69
now in accounts i only find entry for .69 that is the last row updated in the table temp for a given accountid
I m confused with things

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-27 : 07:17:31

Can you let me know output of following query

Begin Tran
Update Temp Set Cost = '.59' Where Country like 'Mexico%'

Select Accounts.Accid,Accounts.Country,Accounts.Cost From
Accounts Inner Join Temp On Accounts.Accid = Temp.AccID Where
Temp.Country like 'Mexico%'
Rollback Tran

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

tripathibhupesh
Starting Member

10 Posts

Posted - 2006-04-27 : 07:31:35

ok i have two rows in temp
accountid callto country calltime_gmt duration cost
invoip0042 52871766594 Mexico - Torreon 2006-04-25 22:49:01.000 241.00 .0090

invoip0043 52871766594 Mexico - Torreon 2006-04-25 22:49:01.000 241.00 .0090

the query results in

accountid debit
invoip0042 .0090
invoip0043 .0090

account doesn have country column
thanks for the prompt reply

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-27 : 07:36:43

then is output fine or you wanted somthing else ??? in your debit column???

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

tripathibhupesh
Starting Member

10 Posts

Posted - 2006-04-27 : 08:06:45

ya thats what i want but when there are multiple rows for the same id
only the last updated rows get reflected in the accounts

suppose
7 rows for invoip0041
than i will get by ur query

invoip0041 2.7000
invoip0041 2.7000
invoip0041 2.7000
invoip0041 2.7000
invoip0041 2.7000
invoip0041 2.7000

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-27 : 08:39:32

Sorry I m not getting it..

Lets take the following Scenario...

In the Temp Table you have following records..

AcountID Country Cost
1000 Mexico 100
1001 Mexico 100

Now you do update like this


Upate Temp Set Cost = 1200 Where Country like 'Mexico%'

Now in the Account Table

AccountID Debit
1000 1200
1000 1200
1000 1200
1001 1200
1001 1200

This should happn or somthing else should happen???

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

tripathibhupesh
Starting Member

10 Posts

Posted - 2006-04-27 : 08:54:43

as per the scenario

accounts table should have

accountid debit
1000 1200
1001 1200

really sorry if i m not able to explain my problem well

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-27 : 09:02:13

but then are you sure Temp and Account Table are linked on AccountId itself..?? or its a composite key .. since in your code(CurSor), they were linked to gather using AccountID only .. so where ever in the accounts table it will find curresponding Temp.Accountid it will update it

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

tripathibhupesh
Starting Member

10 Posts

Posted - 2006-04-27 : 09:26:34

ya both tables are linked with accountid
also in accounts table for an accountid there is only a single row so basically all the update are performed on that single row matched with accountid

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-27 : 09:49:11

Can you do somthing like this for testing perpose
in your trigger..

select i.accountid,datepart(month,i.calltime_gmt),i.calltime_gmt,i.cost,d.cost As Cost2 Into #Temp
From inserted i,deleted d where i.accountid=d.accountid) As Tbl

and at the end of the trigger

Select * From #Temp

Display the output .. i guess there is someproblem with the calculations..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

tripathibhupesh
Starting Member

10 Posts

Posted - 2006-04-27 : 09:59:51

hey thanks a lot for ur time and help

i wrote this peice of code n it wrked

CREATE TRIGGER onup ON temp
FOR update
AS
declare @cost money
declare @oldcost money
declare @calltime datetime
declare @accountid varchar(25)
declare @calltime_gmt datetime
declare uptemp cursor
for
select distinct(accountid) from inserted
open uptemp
fetch next from uptemp into @accountid
while(@@fetch_status<>-1)
begin
if(@@fetch_status<>-2)
begin
select @cost=(select sum(cost) from inserted where accountid=@accountid)
select @oldcost=(select sum(cost) from deleted where accountid=@accountid)
update accounts set debit=(debit-@oldcost+@cost) where accountid=@accountid
end
fetch next from uptemp into @accountid
end
close uptemp
deallocate uptemp

but still have a question

i have trigger for update on table logically that will fire for every update on the table
now if the update statement is such that it updates more than one row than will my trigger fire for evry single row or just the parent update

because if it fires for every single row even than i have all the records in the inserted table generated by the parent update stmt shouldn the only current updated row find an entry in the inserted or deleted table

hey jigar thanks a lot for ur help just clearing my logic

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-27 : 10:08:19

It will only fire once, and in the inserted table you will having all the tables which are just updated..

i personally feel you dont require a cursor for this, since it will really slow down your performance..

just try this update

Update Accounts Set Debit = Debit - f.Cost From
(Select (i.Cost + d.Cost) As Cost, i.AccountID As ActID
From Inserted i Inner Join Deleted d On i.AccountID = d.AccountID) As F
Where
AccountID = F.ActID

And by the way who is Jigar :)??

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

tripathibhupesh
Starting Member

10 Posts

Posted - 2006-04-27 : 10:16:22

hey really soorry misplaced the name
will try out with ur solution
thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources