Urgent, get help for writting trigger.

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)

Urgent, get help for writting trigger.

Author

Topic

kennon2000
Starting Member

7 Posts

Posted - 2005-11-21 : 15:45:43

I have the follow trigger attached to table [VoucherDetail] and I want to update table RecAccount when there is change in [VoucherDetail]:

Create Trigger [dbo].[tr_Account]
ON [dbo].[VoucherDetail]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
Declare @BalanceDiff decimal(18,8),
@AccountID int,
@RecDate DateTime,
@MonthStart DateTime,
@MonthEnd DateTime

-----------Handle INSERT----------------
----Some code here----

-----------Handle DELETE----------------
IF EXISTS(Select AccountID from deleted)
BEGIN
Set @AccountID=(SELECT AccountID From deleted)
Set @RecDate=(SELECT RecDate from deleted)
Set @MonthStart=str(YEAR(@RecDate))+'-'+str(MONTH(@RecDate))+'-01'
Set @MonthEnd=DATEADD(day, -1, DATEADD(month, 1, str(YEAR(@RecDate))+'-'+str(MONTH(@RecDate))+'-01'))

Set @BalanceDiff = dbo.Priv_Acct_GetAccountBalance(@MonthStart, @MonthEnd, @AccountID)

IF EXISTS(Select AccountID from RecAccount Where AccountID=@AccountID AND RefMonth=@MonthStart )
Update RecAccount
Set BalanceDiff= @BalanceDiff
Where AccountID=@AccountID AND RefMonth=@MonthStart
ELSE
BEGIN
Insert RecAccount ( AccountID, RefMonth, BalanceDiff )
Values( @AccountID, @MonthStart, @BalanceDiff )
END
END
END

However, when there is multiple DELETE in table [VoucherDetail], error occur since (SELECT AccountID From deleted) give multiple rows. How should I amend the about trigger to solve this problem?

Thanks in advance.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-21 : 17:34:19

First, you should create a separate trigger to handle your deletes. This is because an UPDATE transaction populates both the deleted and inserted virtual tables. So if you just check for the existence of records in deleted, that does not tell you whether a delete actually occured.
Second, you need to write your trigger so that it can handle set-based transactions. Here is an example for a delete trigger:

Create Trigger [dbo].[tr_Account]
ON [dbo].[VoucherDetail]
AFTER DELETE
AS
BEGIN
update	RecAccount
set	BalanceDiff = dbo.Priv_Acct_GetAccountBalance
		(dateadd(month, datediff(month, 0, deleted.RecDate), 0), --MonthStart
		dateadd(day, -1, dateadd(month, datediff(month, 0, deleted.RecDate)+1, 0)), --MonthEnd
		deleted.AccountID)
from	RecAccount
	inner join deleted
		on RecAccount.AccountID = deleted.AccountID
		and RecAccount.RefMonth = dateadd(month, datediff(month, 0, deleted.RecDate), 0)

insert into RecAccount
	(AccountID,
	RefMonth,
	BalanceDiff)
select	AccountID,
	dateadd(month, datediff(month, 0, deleted.RecDate), 0),
	dbo.Priv_Acct_GetAccountBalance
		(dateadd(month, datediff(month, 0, deleted.RecDate), 0), --MonthStart
		dateadd(day, -1, dateadd(month, datediff(month, 0, deleted.RecDate)+1, 0)), --MonthEnd
		deleted.AccountID)
from	deleted
where not exists
	(select	*
	from	RecAccount
	where	RecAccount.AccountID = deleted.AccountID
		and RecAccount.RefMonth = dateadd(month, datediff(month, 0, deleted.RecDate), 0))
END

Your insert/update trigger will need to be handled similarly.
Third, note the streamlining of your date calculations.
Fourth, this is right on the hairy edge of what is in the appropriate scope of triggers. I understand that you are maintening data integrity with your trigger, which is good, but in this case your trigger is complex enough that it may impact performance. Especially with the user-defined function references and the non-sargable joins. Just a warning.

kennon2000
Starting Member

7 Posts

Posted - 2005-11-21 : 22:29:58

Hi, blindman. Thanks you so much in answering my question. You really teach me a lot!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources