October 12, 2014 at 3:30 am
I need some help and i too new to SQL.
How to i create a Trigger to enforce this constraint:
A rental can be made only if the customer is registered with the company and the car is not
currently rented out. If not, the rental will not be successful.
October 12, 2014 at 4:00 am
I didn't test it, but it should be fine
CREATE TRIGGER triggerName ON tableName
FOR INSERT
AS
IF NOT EXISTS (SELECT 1 FROM customerTable where id = 'customerID')
OR
IF EXISTS (SELECT 1 FROM rentedCarsTable WHERE carID = 'carID' AND rentedStatus = 'True')
BEGIN
ROLLBACK TRANSACTION
GoTo e
END
--its okay to have rental
INSERT INTO rentalTable (carID,customerID)
VALUES ('carID','customerID')
UPDATE rentedCarsTable SET rentedStatus = 'True' WHERE carID = 'carID'
e:
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
October 12, 2014 at 4:44 am
Thanks allot for helping but am getting error message. I have Put in the tables in:
CREATE TABLE Car
(
[VehNo] Varchar(8) NOT NULL,
[Brand] Varchar(64) NOT NULL,
[Model] Varchar(64) NOT NULL,
[DateRegistered] DATE NOT NULL,
[PurchasePrice] decimal(8,2) NOT NULL,
[RentalCost] money NOT NULL,
[Contition] char(20) NOT NULL DEFAULT 'excellent',
CONSTRAINT VehNo_PK PRIMARY KEY([VehNo]),
CONSTRAINT VehNoValues CHECK([VehNo] like '[A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9][A-Z]'),
CONSTRAINT RentalCostValues CHECK([RentalCost] BETWEEN 120 AND 500),
CONSTRAINT ContitionValues CHECK([Contition] LIKE 'excellent' OR
[Contition] LIKE'above average' OR [Contition] LIKE'average' OR
[Contition] LIKE'needs repair')
)
;
CREATE TABLE CUSTOMER(
[CUSTNO] varchar(5) NOT NULL,
[ID] CHAR(9) NOT NULL,
[NAME] VARCHAR(128) NOT NULL,
[ADDRESS] VARCHAR(128) NOT NULL,
[DATEOFBIRTH] DATE NOT NULL,
CONSTRAINT CUSTNOPK PRIMARY KEY([CUSTNO]),
CONSTRAINT CUSTNOVALUES CHECK(CUSTNO LIKE '[A-Z][0-9][0-9][0-9][0-9]'),
CONSTRAINT DATEOFBIRTHVALUES CHECK ( getdate() - [DATEOFBIRTH]=21),
);
CREATE TABLE RENTAL
(
[RENTALNO] INT IDENTITY (1000,1)NOT NULL,
[VEHNO] VARCHAR(8) NOT NULL,
[CUSTNO]varchar(5) NOT NULL,
[DATERENTED] DATE NOT NULL,
[DATERETURNED] DATE NULL,
[CONDITION] CHAR(20) NULL DEFAULT 'EXCELLENT',
CHECK ((DATERETURNED >= DATERENTED) AND(DATERETURNED != DATERENTED)),
CHECK([CONDITION] LIKE 'EXCELLENT' OR
[CONDITION] LIKE 'ABOVE AVERAGE' OR [CONDITION] LIKE'AVERAGE' OR
[CONDITION] LIKE'NEEDS REPAIR'),
CONSTRAINT RENTALNO_PK PRIMARY KEY(RENTALNO),
CONSTRAINT VEHNOFK FOREIGN KEY(VEHNO)
REFERENCES Car(VEHNO)
ON DELETE NO ACTION
ON UPDATE CASCADE,
CONSTRAINT CUSTNOFK FOREIGN KEY(CUSTNO)
REFERENCES CUSTOMER(CUSTNO)
ON DELETE NO ACTION
ON UPDATE CASCADE
)
;
CREATE TABLE Payment
(
[RentalNo] INT IDENTITY (1000,1)NOT NULL,
[CreditCardNo] varchar(12) NOT NULL,
[Amount] money NOT NULL,
[PaymentDate] DATE NOT NULL,
[Status] CHAR(20) NOT NULL,
CONSTRAINT RentalNoPK PRIMARY KEY([RentalNo]),
CONSTRAINT RentalNoFK FOREIGN KEY([RentalNo])
REFERENCES Rental(RentalNo)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT AmountValue CHECK([Amount] BETWEEN '120' AND '10000'),
CONSTRAINT StatusValues CHECK([Status] LIKE 'processed' OR
[Status] LIKE'billed' OR [Status] LIKE'received' OR
[Status] LIKE'error'OR [Status] LIKE'default')
)
;
October 12, 2014 at 5:11 am
Sorry it had syntax error, try this:
CREATE TRIGGER triggerName ON test.dbo.rental
FOR INSERT
AS
DECLARE @VEHNO AS NVARCHAR(8)
DECLARE @CUSTNO AS NVARCHAR(5)
SELECT @VEHNO=VEHNO,@CUSTNO=CUSTNO FROM INSERTED
IF
(
(NOT EXISTS (SELECT 1 FROM test.dbo.CUSTOMER where id = @CUSTNO))
OR
(EXISTS (SELECT 1 FROM test.dbo.RENTAL WHERE VEHNO = @VEHNO AND DATERETURNED IS NULL))
)
BEGIN
ROLLBACK TRANSACTION
GoTo e
END
--its okay to have rental put your additional query here if needed
e:
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
October 12, 2014 at 6:45 am
Quick thought, be careful when implementing a trigger with a single value logic and a transaction rollback, chances are that this will cause problems down the line. A more robust approach is to eliminate non qualifying entries from the insert, here is an example:
😎
CREATE TRIGGER triggerName ON dbo.rental
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO dbo.RENTAL
(
CONDITION
,CUSTNO
,DATERENTED
,DATERETURNED
,VEHNO
)
SELECT
I.CONDITION
,I.CUSTNO
,I.DATERENTED
,I.DATERETURNED
,I.VEHNO
FROM dbo.CUSTOMER C
INNER JOIN inserted I
ON C.CUSTNO = I.CUSTNO
INNER JOIN dbo.RENTAL R
ON I.VEHNO = R.VEHNO
WHERE R.DATERETURNED IS NULL;
END
The trigger can be expanded to add an insert to a failure audit table etc.
October 12, 2014 at 9:05 am
I wouldn't use a trigger for this (especially since you've not used triggers before). A trigger would be "spaghetti code" where you throw something against the wall to see if it will stick and roll back if it doesn't. It's a huge waste of clock cycles and I/O and rollbacks are comparatively very expensive.
Yes, you could write an "Instead Of" trigger" but there's just no need for such pains.
Write the check into a correct stored procedure that first checks for the conditions that you've identified and either produces/returns a meaningful error message if one or more of the conditions break or commits the data if it meets all conditions.
--Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply