Trigger help - 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)

Trigger help

Author

Topic

dupati1
Posting Yak Master

123 Posts

Posted - 2005-10-07 : 17:14:46


CREATE TRIGGER mytrigger
ON VendorTemp
FOR INSERT,UPDATE
AS
IF UPDATE(MoveIt)=1
Begin
INSERT INTO Vendor
(VendorID,VendorName)
Select Ins.VendorID, Ins.VendorName
FROM Inserted Ins

INSERT INTO VendorContactInfo
(VendorID,VendorAddress,VendorPhone)
Select Ins.VendorID,Ins.VendorAddress,Ins.VendorPhone
FROM Inserted Ins

INSERT INTO VendorBusinessInfo
(VendorID, VendorSpecialty)
SELECT Ins.VendorID,Ins.VendorSpecialty

End

Go

this is my first attempt at writing triggers.So
wasnt sure if it was correct. How can i add
error checking and roll back thing to it(if needed)

and sometimes my VendorSpecialty field holds multiple
values separated by vbCrLf...something like

'Mechanical Chemical Electrical'

how do i change my insert statement inside the trigger
to get something like

VendorID | VendorSpecialty
____________________________

101 | Mechanical
101 | Chemical
101 | Electrical

Thanks in advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-10 : 02:25:53

Use split function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-10-10 : 04:26:57

maddy : I think dupati1 was using the "|" character to indicate column names, not that the data is in delimited format.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-10 : 04:41:09

quote:
and sometimes my VendorSpecialty field holds multiple
values separated by vbCrLf...something like

'Mechanical Chemical Electrical'

how do i change my insert statement inside the trigger
to get something like

VendorID | VendorSpecialty
____________________________

101 | Mechanical
101 | Chemical
101 | Electrical

So I thought he/she needs spliting it

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-10-10 : 06:32:52

Ah, for that bit - yup, sorry about that.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-10 : 06:44:08

quote:
Originally posted by Kristen

Ah, for that bit - yup, sorry about that.

Kristen

No problem Kris

Madhivanan

Failing to plan is Planning to fail

dupati1
Posting Yak Master

123 Posts

Posted - 2005-10-10 : 08:35:20

Thanks for the replies guys...but one question though...does my trigger look ok to you guys...

because this was my first attempt to create one and i wrote it after reading BOL...how can i put some error checking and roll back if needed...

Thanks so much

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-10 : 08:50:06

hi you can try this ..

CREATE TRIGGER mytrigger
ON VendorTemp
FOR INSERT,UPDATE
AS
IF UPDATE(MoveIt)=1
Begin

Begin Tran
Declare @ErrVendor int,@errVendorContinfo int,@errVendorBussinessInfo

INSERT INTO Vendor
(VendorID,VendorName)
Select Ins.VendorID, Ins.VendorName
FROM Inserted Ins
Select @ErrVendor= @@Error

INSERT INTO VendorContactInfo
(VendorID,VendorAddress,VendorPhone)
Select Ins.VendorID,Ins.VendorAddress,Ins.VendorPhone
FROM Inserted Ins
Select @errVendorContinfo= @@Error

INSERT INTO VendorBusinessInfo
(VendorID, VendorSpecialty)
SELECT Ins.VendorID,Ins.VendorSpecialty

Select @errVendorBussinessInfo= @@Error

if @@Error <> 0
Begin
if @errVendorBussinessInfo <> 0
print 'Error in Inserting Vendor Business info '
if @errVendorContinfo<> 0
print 'Error in Inserting Vendor Contact info '
if @ErrVendor<> 0
print 'Error in Inserting Vendor '

Rollback Tran
End
Else
Commit Tran

End

Go

for the error checking .. i hope this helps you ..

Complicated things can be done by simple thinking

dupati1
Posting Yak Master

123 Posts

Posted - 2005-10-10 : 09:20:51

Thank you chiragkhabaria,

I will try it out...

Kristen
Test

22859 Posts

Posted - 2005-10-10 : 09:33:56

if @@Error <> 0
Begin
...

is only going to react to the outcome of the third INSERT statement isn't it?

The ROLLBACK is needed if any of the three error capture variables are non-zero.

This:

INSERT INTO VendorBusinessInfo
(VendorID, VendorSpecialty)
SELECT Ins.VendorID,Ins.VendorSpecialty

needs

FROM Inserted Ins

adding doesn't it?

What is intended to happen on an ~~INSERT~~ UPDATE - as the values will already exist in the target tables won't they?

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-10 : 09:35:57

More Error Handlings

http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-10 : 09:39:47

opps .. My mistake........:-((...

if @errVendorBussinessInfo <> 0 or @errVendorContinfo<> 0 or @ErrVendor<> 0
Begin
if @errVendorBussinessInfo <> 0
print 'Error in Inserting Vendor Business info '
if @errVendorContinfo<> 0
print 'Error in Inserting Vendor Contact info '
if @ErrVendor<> 0
print 'Error in Inserting Vendor '

Rollback Tran
End
Else
Commit Tran

End

I guess this should be right.. Thanks.. Kris.. :-)

Complicated things can be done by simple thinking

dupati1
Posting Yak Master

123 Posts

Posted - 2005-10-10 : 11:30:11

Thank you all...you guys have been so helpful...

dupati1
Posting Yak Master

123 Posts

Posted - 2005-10-10 : 11:45:31

Guys, i have one question...Cant i just declare one error variable instead of three...

Thanks

Kristen
Test

22859 Posts

Posted - 2005-10-10 : 12:19:40

Hmmmm ... well yes, but the moment you have examined @@ERROR its value is gone. So you need to do something useful with it straight away.

DECLARE @strErrMsg varchar(1000)

...

INSERT INTO Vendor
(VendorID,VendorName)
Select Ins.VendorID, Ins.VendorName
FROM Inserted Ins
IF @@Error <> 0 SELECT @strErrMsg = COALESCE(@strErrMsg+', ', '') + 'Error in Vendor'

INSERT INTO VendorContactInfo
(VendorID,VendorAddress,VendorPhone)
Select Ins.VendorID,Ins.VendorAddress,Ins.VendorPhone
FROM Inserted Ins
IF @@Error <> 0 SELECT @strErrMsg = COALESCE(@strErrMsg+', ', '') + 'Error in VendorContactInfo'

...

if @strErrMsg IS NOT NULL
Begin
print @strErrMsg -- Probably better to use RAISERROR here

Rollback Tran
End
Else
Commit Tran

End

Kristen

dupati1
Posting Yak Master

123 Posts

Posted - 2005-10-10 : 13:17:24

Thanks Kristen,

Thats Awesome.

dupati1
Posting Yak Master

123 Posts

Posted - 2005-10-10 : 13:42:49

Kristen,

I am getting errors

1. on line IF UPDATE(MoveIt)=1, it says incorrect syntax near '=' sign
2. at the end, it says incorrect syntax near keyword END

dupati1
Posting Yak Master

123 Posts

Posted - 2005-10-10 : 14:05:11

ok fixed both the problems...thanks

Kristen
Test

22859 Posts

Posted - 2005-10-10 : 14:43:22

"thanks"

Who me? OK, I'll take a bow anyway!