Trigger - 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

Trigger

Author

Topic

Mohamed Faisal
Yak Posting Veteran

51 Posts

Posted - 2012-09-27 : 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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-27 : 12:00:52

sorry your current table definition doesnt make much sense

how can identity based referenceNum column of ServiceRenderedTable be linked to identity column referenceNum in CaseTable by a foreign key? I think it should be a different column in ServiceRenderedTable of non identity type otherwise you cant guarantee referential integrity.

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

Mohamed Faisal
Yak Posting Veteran

51 Posts

Posted - 2012-09-27 : 12:18:51

Hi visakh16

I just following an exercise. Which has ServiceRendered has a primary key for the referenceNumber and is also the foreign Key to Casetable.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-27 : 12:33:39

but in case Service Rendered cant be identity if it has a fk relationship to casetable
logically also it doesnt make much sense. How can unique identified value for Service be same as Unique identified value for related case?
i think there should be casereferencenum in ServiceRendered table which has to be linked ro referencenum of Case table by means of foreign key

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-27 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources