|
Mohamed Faisal
Starting Member
26 Posts |
 Posted - 09/27/2012 : 11:51:25
|
Hi there,
I new to SQL.
My Code below:
CREATE TABLE CaseTable
(
referenceNum int NOT NULL IDENTITY(100000,1),
startDate DATETIME NOT NULL,
endDate DATETIME NULL,
caseDetail Char(255) NOT NULL,
caseType Char NOT NULL DEFAULT'copyright and trademark',
clientID Char(5) NOT NULL,
CONSTRAINT CaseTablePK PRIMARY KEY(referenceNum),
CONSTRAINT ClientTableFK FOREIGN KEY(clientID)
REFERENCES ClientTable(clientID),
CONSTRAINT referenceValues CHECK(referenceNum like 'intellectual property enforcement, copyright and trademark, patent and industrial design, trade secret, risk management' OR
referenceNum like'litigation'),
CONSTRAINT clientIDValues CHECK(clientID like '[A-Z][0-9][0-9][0-9][A-Z]')
);
CREATE TABLE ServiceTable
(
serviceCode Char(3) NOT NULL,
serviceDescription Char(255) NOT NULL,
minCharge numeric NOT NULL,
CONSTRAINT ServiceTablePK PRIMARY KEY(serviceCode),
CONSTRAINT serviceCodeValues CHECK(serviceCode like '[C][0-9][0-9]' OR
serviceCode like'[E][0-9][0-9]' OR
serviceCode like'[O][0-9][0-9]'),
CONSTRAINT minChargeValues CHECK(minCharge BETWEEN '500' AND '1000000')
);
CREATE TABLE ServiceRenderedTable
(
referenceNum int NOT NULL IDENTITY(100000,1),
serviceDate DATETIME NOT NULL,
serviceCode Char(3) NOT NULL,
actualServiceCost MONEY NOT NULL ,
CONSTRAINT ServiceRenderedTablePK PRIMARY KEY(referenceNum,serviceDate),
CONSTRAINT CaseTableFK FOREIGN KEY(referenceNum)
REFERENCES CaseTable(referenceNum),
CONSTRAINT ServiceTableFK FOREIGN KEY(serviceCode)
REFERENCES ServiceTable(serviceCode)
CONSTRAINT actualService_positive CHECK (actualService>0.00)
);
How do I create a trigger where serviceDate in ServiceRenderedTable table should not be earlier than the startDate for CaseTable. |
|
|
Lamprey
Flowing Fount of Yak Knowledge
3833 Posts |
Posted - 09/27/2012 : 12:42:34
|
Visakh is right the schema makes no sense. But, here is a trigger to get you going.CREATE TRIGGER dbo.ServiceRenderedTableAfterInsertUpdate
ON dbo.ServiceRenderedTable FOR INSERT, UPDATE
AS
BEGIN
IF EXISTS
(
SELECT
*
FROM
inserted AS I
INNER JOIN
dbo.CaseTable AS CT
ON I.referenceNum = CT.referenceNum
WHERE
CT.ServiceDate < I.StartDate
)
BEGIN
RAISERROR ('SeriveDate before StartDate', 16, 1)
ROLLBACK TRAN
END
END
GO
|
 |
|
Trigger
