Replacing Triggers with Stored Proc

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 2005 Forums

Transact-SQL (2005)

Replacing Triggers with Stored Proc

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2008-11-12 : 01:28:11

hi

I have this piece of code for trigger and I would like to convert it to a stored proc. The reason i am changing it to SP is because at times i need to directly change the data in the DB and it always get triggered and i don't want that to happen. How should i go about it? Any advice or guides would be greatly appreciated.

ALTER TRIGGER [dbo].[trgUpdate2]
on [dbo].[tblCodeDetail]
After Update
AS
Declare @CLCID as int
Declare @CLCID2 as int
Declare @CLD_VoucherID as Nvarchar(20)
if UPDATE(CLCID)
Select @CLCID = (Select CLCID from Inserted)
Select @CLCID2 = (Select CLCID from deleted)
Select @CLD_VoucherID = (Select CLD_VoucherID from Inserted)

BEGIN
SET NOCOUNT ON;
update dbo.tblCode
set CLC_Size = CLC_Size + 1
from inserted join tblCode on inserted.CLC_Code = tblCode.CLC_Code

update dbo.tblCode
set CLC_CValue = (tblCode.CLC_CValue + inserted.CLD_Price),
CLC_EValue = (tblCode.CLC_EValue + inserted.CLD_EFee)
from inserted join tblCode on inserted.CLC_Code = tblCode.CLC_Code

update dbo.tblCode
set CLC_Size = CLC_Size - 1
from Deleted join tblCode on Deleted.CLC_Code = tblCode.CLC_Code

update dbo.tblCode
set CLC_CValue = (tblCode.CLC_CValue - deleted.CLD_Price),
CLC_EValue = (tblCode.CLC_EValue - deleted.CLD_EFee)
from Deleted join tblCode on Deleted.CLC_Code = tblCode.CLC_Code

if (@CLD_VoucherID = '000-000')
Begin
SET NOCOUNT ON;
Update tblVoucher
set issueTo = 0
from Inserted where Inserted.STU = tblVoucher.issueTo
end
else if (@CLD_VoucherID <> '000-000')
Begin
SET NOCOUNT ON;
Update tblVoucher
set issueTo = (Select STU from Inserted)
from Inserted join dbo.tblVoucher on Inserted.CLD_VoucherID = dbo.tblVoucher.EVO_VoucherID
end

END

ALTER TRIGGER [dbo].[trgUpdateValue] on [dbo].[tblCodeDetail]
After INSERT,DELETE
AS
if (Select Count(*) from inserted) <> 0
BEGIN
SET NOCOUNT ON;
update dbo.tblCode
set CLC_CValue = (tblCode.CLC_CValue + inserted.CLD_Price),
CLC_EValue = (tblCode.CLC_EValue + inserted.CLD_EFee)
from inserted join tblCode on inserted.CLC_Code = tblCode.CLC_Code
END
else if (Select Count(*) from Deleted) <> 0
BEGIN
SET NOCOUNT ON;
update dbo.tblCode
set CLC_CValue = (tblCode.CLC_CValue - deleted.CLD_Price),
CLC_EValue = (tblCode.CLC_EValue - deleted.CLD_EFee)
from Deleted join tblCode on Deleted.CLC_Code = tblCode.CLC_Code
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 01:37:55

what is primary key of tblCodeDetail? is it an identity column?

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2008-11-12 : 03:27:49

hi

It is CLCID. It is not an identity column. It's datatype is Nvarchar. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 03:36:03

quote:
Originally posted by sg2255551

hi

It is CLCID. It is not an identity column. It's datatype is Nvarchar. Thanks

so how do you generate value for CLID during insertion of each new record?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-12 : 04:30:06

[code]ALTER TRIGGER dbo.trgUpdate2
ON dbo.tblCodeDetail
AFTER UPDATE
AS

SET NOCOUNT ON

IF UPDATE(clcID) AND SUSER_SNAME() <> 'MyID'
BEGIN
UPDATE c
SET c.CLC_CValue = c.CLC_CValue + i.CLD_Price - d.CLD_Price,
c.CLC_EValue = c.CLC_EValue + i.CLD_EFee - d.CLD_EFee
FROM dbo.tblCode AS c
INNER JOIN inserted AS i ON i.CLC_Code = c.CLC_Code
INNER JOIN deleted AS d ON d.CLC_Code = c.CLC_Code

UPDATE v
SET v.issueTo = 0
FROM tblVoucher AS v
INNER JOIN inserted AS i ON i.STU = v.issueTo
WHERE i.CLD_VoucherID = '000-000'

UPDATE v
SET v.issueTo = i.STU
FROM tblVoucher AS v
INNER JOIN inserted AS i ON i.CLD_VoucherID = v.EVO_VoucherID
WHERE i.CLD_VoucherID <> '000-000'
END[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-12 : 04:30:17

[code]ALTER TRIGGER dbo.trgUpdateValue
ON dbo.tblCodeDetail
AFTER INSERT,
DELETE
AS

SET NOCOUNT ON

IF SUSER_SNAME() <> 'MyID'
BEGIN
UPDATE c
SET c.CLC_CValue = c.CLC_CValue + i.CLD_Price,
c.CLC_EValue = c.CLC_EValue + i.CLD_EFee
FROM dbo.tblCode AS c
INNER JOIN inserted AS i ON i.CLC_Code = c.CLC_Code

UPDATE c
SET c.CLC_CValue = c.CLC_CValue - d.CLD_Price,
c.CLC_EValue = c.CLC_EValue - d.CLD_EFee
FROM dbo.tblCode AS c
INNER JOIN deleted AS d ON d.CLC_Code = c.CLC_Code
END[/code]

E 12°55'05.63"
N 56°04'39.26"

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2008-11-12 : 09:24:10

hi

The CLCID is generated by users

Thanks Peso. The SUser_SName() should do the trick.

Also, comparing triggers and Stored Proc which one is easier to maintain?

I have a friend who prefer to use SP over triggers which he claims to have better control. If that is the case, how should i go about it switching from TRG to SP based on the code i have posted in this post? Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources