Trigger with a RAISERROR and ELSE case issue
I think there is a simpler way to check for expiration:
CREATE TRIGGER BadCreditCardDate
ON Sales.CreditCard
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS (
SELECT 1
FROM inserted
WHERE (YEAR(GETDATE()) > ExpYear) OR (YEAR(GETDATE()) = ExpYear AND MONTH(GETDATE()) > ExpMonth)
)
BEGIN
RAISERROR ('The Credit Card you have entered has expired.' ,10,1)
ROLLBACK TRANSACTION
END
ELSE
BEGIN
INSERT INTO CreditCard (CardType, CardNumber, ExpMonth, ExpYear, ModifiedDate)
SELECT CardType, CardNumber, ExpMonth, ExpYear, ModifiedDate
FROM inserted
END
END
In this way you effectively check every record to be inserted in CreditCard
.
MikeyZ
Updated on January 26, 2020Comments
-
MikeyZ over 4 years
I am trying to make a bit of code that takes in 2 separate columns, a month and a year. From there I want it to see if those numbers entered have already passed or not. If they have passed, cause an error to pass and stop the transaction. Otherwise, I want it to continue on and insert new information into the table. I know I am close on getting this to work, but I cant seem to get the RAISERROR to fire. I am sure it has to do with the fact I am pretty new at this and I am missing some small detail.
Currently I am taking the two months in as variables and the making a third variable to use to turn the other two into a proper datetime format. Then I use the datediff function to try and see if it has passed that way. To no avail though. I keep getting the insert function going, even if the card date is old.
USE AdventureWorks2012 GO CREATE TRIGGER BadCreditCardDate ON Sales.CreditCard INSTEAD OF INSERT AS Begin DECLARE @ExpMonth tinyint, @ExpYear smallint, @ExpMonthYear datetime SELECT @ExpMonth=ExpMonth, @ExpYear=ExpYear, @ExpMonthYear = @ExpYear + '-' + @ExpMonth + '-00' FROM INSERTED IF DATEDIFF(MONTH,@ExpMonthYear,GETDATE()) < 0 BEGIN RAISERROR ('The Credit Card you have entered has expired.' ,10,1) ROLLBACK TRANSACTION END ELSE Begin INSERT INTO CreditCard (CardType, CardNumber, ExpMonth, ExpYear, ModifiedDate) Select CardType, CardNumber, ExpMonth, ExpYear, ModifiedDate FROM inserted END End
-
MikeyZ over 9 yearsThis worked like a charm. I am going to have to look over this bit of code to see how its working and then remember this little beauty.