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

General SQL Server Forums

New to SQL Server Programming

Triggers

Author

Topic

aidmondo
Starting Member

23 Posts

Posted - 2012-05-13 : 06:51:04

Create table PaymentMethods(
PaymentMethodID int Primary Key,
Description not null
)

Create table Payments(
PaymentID int Primary Key,
PaymentMenthodID int Constraint cfkPay Foreign Key References (#above) not null,
ChequeNo int not null
)

Now my question is ChequeNo should be entered if the description is cheque and if not it should be left blank. How?

aidmondo

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-13 : 06:59:07

[code]First of all this should be your tables design

Create table PaymentMethods(
PaymentMethodID int Primary Key,
Description varchar(200)not null
)

Create table Payments(
PaymentID int Primary Key,
PaymentMenthodID int Constraint cfkPay References PaymentMethods (PaymentMethodID) not null,
ChequeNo int not null
)[/code]

Vijay is here to learn something from you guys.

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-13 : 07:03:25

[code]What value do you want to insert into chequeno column.Can you please provide some sample data.[/code]

Vijay is here to learn something from you guys.

aidmondo
Starting Member

23 Posts

Posted - 2012-05-13 : 12:39:15

If Description is Cheque, ChequeNo is 2001100547701
If Description is Cash, ChequeNo is Null

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-13 : 13:51:48

quote:
Originally posted by aidmondo

If Description is Cheque, ChequeNo is 2001100547701
If Description is Cash, ChequeNo is Null


Try below code with table design what I have created .And make sure there are only two option should be either cheque or cash ...if apart from cash yo insert demand draft then it will also insert NULL value.





Create table PaymentMethods(
PaymentMethodID int Primary Key,
Description varchar(200)not null
)

Create table Payments(
PaymentID int Primary Key identity (1,1),
PaymentMenthodID int Constraint cfkPay References PaymentMethods (PaymentMethodID) not null,
ChequeNo varchar(30) 
)


create  trigger trg on PaymentMethods
after insert 
as

SET NOCOUNT ON
	
 declare @Description varchar(20)
 declare @PaymentMethodID varchar(20)
 select @Description= i.Description from inserted i
 select @PaymentMethodID = i.PaymentMethodID  from inserted i


 if @Description = 'CHecque'
 insert into Payments (PaymentMenthodID,ChequeNo)values( @PaymentMethodID,'2001100547701')
 else 
 insert into Payments (PaymentMenthodID,ChequeNo)values(@PaymentMethodID,null)

 
 
 
insert into PaymentMethods values (1,'CHecque')
select * from Payments

insert into PaymentMethods values(2,'cash')
select * from Payments

Vijay is here to learn something from you guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-13 : 14:02:10

The best way is implement it by means of UDF and use it in CHECK constraint



CREATE FUNCTION dbo.ChequeNoValidate
(
@ChequeNo varchar(30),
@PaymentMethodID int
)
RETURNS Bit
AS
BEGIN
DECLARE @Ret bit,@PaymentMethod varchar(200)

SET @Ret =1

SELECT @PaymentMethod = Description
FROM PaymentMethods
WHERE PaymentMethodID = @PaymentMethodID

IF (@PaymentMethod  = 'Cheque' AND @ChequeNo IS NULL)
OR (@PaymentMethod  = 'Cash' AND @ChequeNo IS NOT NULL)
SELECT @Ret = 0 

RETURN (@Ret)
END

Create table PaymentMethods(
PaymentMethodID int Primary Key,
Description varchar(200)not null
)

Create table Payments(
PaymentID int Primary Key identity (1,1),
PaymentMenthodID int Constraint cfkPay References PaymentMethods (PaymentMethodID) not null,
ChequeNo varchar(30) not null
)

ALTER TABLE Payments
ADD CONSTRAINT chkCheckNo CHECK (dbo.ChequeNoValidate(ChequeNo,@PaymentMethodID) = 1 );

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-13 : 14:03:06

quote:

Originally posted by vijays3

quote:
Originally posted by aidmondo

If Description is Cheque, ChequeNo is 2001100547701
If Description is Cash, ChequeNo is Null


Try below code with table design what I have created .And make sure there are only two option should be either cheque or cash ...if apart from cash yo insert demand draft then it will also insert NULL value.





Create table PaymentMethods(
PaymentMethodID int Primary Key,
Description varchar(200)not null
)

Create table Payments(
PaymentID int Primary Key identity (1,1),
PaymentMenthodID int Constraint cfkPay References PaymentMethods (PaymentMethodID) not null,
ChequeNo varchar(30) 
)


create  trigger trg on PaymentMethods
after insert 
as

SET NOCOUNT ON
	
 declare @Description varchar(20)
 declare @PaymentMethodID varchar(20)
 select @Description= i.Description from inserted i
 select @PaymentMethodID = i.PaymentMethodID  from inserted i


 if @Description = 'CHecque'
 insert into Payments (PaymentMenthodID,ChequeNo)values( @PaymentMethodID,'2001100547701')
 else 
 insert into Payments (PaymentMenthodID,ChequeNo)values(@PaymentMethodID,null)

 
 
 
insert into PaymentMethods values (1,'CHecque')
select * from Payments

insert into PaymentMethods values(2,'cash')
select * from Payments

Vijay is here to learn something from you guys.

In this trigger you're assuming only single rows inserts happen.
what if it was a batch insertion?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-13 : 14:05:42

i was assuming single row insertion ...Thanks for your Define function

Vijay is here to learn something from you guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-13 : 14:25:56

quote:
Originally posted by vijays3

i was assuming single row insertion ...Thanks for your Define function

Vijay is here to learn something from you guys.

No problem you're welcome
You can achieve it using INSTEAD OF INSERT trigger also

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources