problem with a trigger statement

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)

problem with a trigger statement

Author

Topic

raymon2683
Starting Member

5 Posts

Posted - 2005-11-30 : 06:59:24

hi
i have a table this is it:
CREATE TABLE Payments (

vPaymentId varchar(50) not NULL ,
iPaymentAmount int NOT NULL
CHECK (iPaymentAmount > 0),
dPaymentDate Datetime DEFAULT getdate(),
iCreditCardNumber int NULL,
cCardHolderName char(50) NULL,
dCreditCardExpDate datetime NULL,
vPaymentMethodId varchar(25)not NULL foreign key references Payment_Methods (vPaymentMethodId ),
cEventId int not null foreign key references Events (cEventId),
iFeeScheduleId int not null foreign key references
Fee_Schedules(iFeeScheduleId),
PRIMARY KEY (vPaymentId)
)
iwant to createa trigger on insert in that table if the vPaymentMethodId is 'Credit card', that will check if some value are entered in the 3 null column(iCreditCardNumber,cCardHolderName,dCreditCardExpDate ).
if the value match the properties the insert should happen else rollback and display a user define error.
but if the vPaymentMethodId is'Cash' these fields should remain blank.thanks

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-30 : 07:13:45

something like this ..

Create Trigger Ins_Payments
For Insert
As

If Exists (Select * From Inserted Where vPaymentMethodId = 'Credit Card')
Begin
-- Check for the property..
End
Else IF Exists (Select * From Inserted Where vPaymentMethodId = 'Cash')
Begin
Update Payments Set iCreditCardNumber = '' ,CardHolderName = '',
dCreditCardExpDate = '' Where iPaymentAmount In (Select iPaymentAmount From Inserted Where vPaymentMethodId = 'Cash')
End

Hope this helps..

Sucess Comes to those who Believe in Beauty of their Dream..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-30 : 07:15:30

refer to Books OnLine for trigger details. Also search for trigger in the Article section of this site

create trigger yourtrigger on yourtable for insert
as
begin
-- place your code here
end

-----------------
[KH]

raymon2683
Starting Member

5 Posts

Posted - 2005-11-30 : 11:06:11

thanks i try but i get some erroe with the check for propertie , because , if nothing is inserted in the nulls columns when credit card it shoul display an error to the user.
how could i check , when i try using '@...= null'
it give me an error

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-30 : 20:19:03

use '@yourvariable is null'

-----------------
[KH]

raymon2683
Starting Member

5 Posts

Posted - 2005-12-04 : 14:48:13

this the trugger that i try , but when inserting somme values it will show "command successfuly" but not insert in the table or it will give an error message saying talking of an error conversion of " Cash " in the last IF statement.
Create Trigger trgIns_Payments on payments
For Insert
As
begin
declare @insPaymentMethodId varchar(25),
@insCreditCardNumber int ,
@insCardHolderName char(50),
@insCreditCardExpDate datetime
If Exists (Select * From Inserted Where vPaymentMethodId = 'Credit Card')
Begin
-- Check for the property..
update Payments
set iCreditCardNumber = @insPaymentMethodId,
cCardHolderName = @insCardHolderName,
dCreditCardExpDate = @insCreditCardExpDate
where vPaymentMethodId in (Select iPaymentAmount From Inserted Where vPaymentMethodId ='Credit Card')
if @insPaymentMethodId is null
begin
raiserror ('Please enter a CreDit Card Number',10,1)
rollback tran
end
if @insCardHolderName is null
begin
raiserror ('Please enter the Card holder name',10,2)
rollback tran
end
if @insCreditCardExpDate is null
begin
raiserror ('Please enter the Expiry Date of the Card',10,1)
rollback tran
end
End

Else
IF Exists (Select * From Inserted Where vPaymentMethodId = 'Cash')
Begin
Update Payments Set iCreditCardNumber = '' ,cCardHolderName = '',
dCreditCardExpDate = '' Where iPaymentAmount In (Select iPaymentAmount From Inserted Where vPaymentMethodId = 'Cash')
End
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources