SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mohamed Faisal
Yak Posting Veteran

51 Posts

Posted - 09/27/2012 :  11:51:25  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 09/27/2012 :  12:00:52  Show Profile  Reply with Quote
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/

Go to Top of Page

Mohamed Faisal
Yak Posting Veteran

51 Posts

Posted - 09/27/2012 :  12:18:51  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 09/27/2012 :  12:33:39  Show Profile  Reply with Quote
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/

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/27/2012 :  12:42:34  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000