July 18, 2009 at 3:09 am
HI
I have trying to program a checking constraint or at least I think this is what I need to do the following.
I have two related tables the:
CREATE TABLE tblJobs
(JobID int IDENTITY(1,1) NOT NULL,
Campaign nvarchar(15) NOT NULL,
ProductIDref nvarchar(10) NOT NULL,
Batch nvarchar(10) NOT NULL,
PRIMARY KEY (ProductIDref, Batch)
CREATE TABLE tblJobsBR
(BRJobIDref int NOT NULL,
JobType nvarchar(6) NOT NULL,
EquipOrTrain nvarchar(50) NOT NULL,
Plant nvarchar(2) NOT NULL,
PlannedStartDate Date NOT NULL,
PlannedEndDate Date NOT NULL,
PlannedEndTime Time NOT NULL DEFAULT ('23:59:00'),
ActualStartDate Date NULL,
ActualStartTime Time NULL,
ActualEndDate Date NULL,
ActualEndTime Time NULL,
IsApproved bit NULL DEFAULT ((0)),
Yield smallint NULL DEFAULT ((0)),
PRIMARY KEY (BRJobIDref)
);
ALTER TABLE tblJobsBR
ADD CONSTRAINT rel_JobsBR_BRJobIDRef_JobID
FOREIGN KEY (BRJobIDRef) REFERENCES tblJobs (JobID)
ON UPDATE CASCADE
ON DELETE CASCADE;
Because some products have yields and some don’t I was not able to set the Yield field in tblJobsBR to NOT NULL, however if a particular product does have a yield I want to be able to force the user to enter it. I also had to set the ActualStartDate and ActualStartTime to NULL because the Planned..Dates may be entered week before hand. How can I ensure the once the ActualStartDate is entered i.e. Is Not NULL anymore the ActualStartTime is also entered. I think the Yield must be > 0 once so long as the ProductIDref is not x y or z product is tricker as the info is in a related table.
Can anyone point me in the write direction as to how to implement this? Mybe it makes more sense not allow 0 unless products are xy or z as there are a lot of products (100+) but only about 8 which do not require a yield. Problem is people keep forgetting to enter it.
Thank you very much
Asta
July 18, 2009 at 7:02 am
How can I ensure the once the ActualStartDate is entered i.e. Is Not NULL anymore the ActualStartTime is also entered.
This can be enforced with a check constraint
ALTER TABLE tblJobsBR
ADD CONSTRAINT tblJobsBR_CK_ActualStart CHECK
((ActualStartDate IS NULL AND ActualStartTime IS NULL)
OR(ActualStartDate IS NOT NULL AND ActualStartTime IS NOT NULL)
)
Because some products have yields and some don’t I was not able to set the Yield field in tblJobsBR to NOT NULL, however if a particular product does have a yield I want to be able to force the user to enter it.
To implement this business rule, recommend a trigger such as
CREATE TRIGGER tblJobsBR_tia_100 ON tblJobsBR AFTER INSERT
AS
SET NOCOUNT ON
IF0 = (SELECT COUNT(*) FROM inserted) RETURN
IFEXISTS
(SELECT 1
FROMinserted
JOINtblJobs
ON tblJobs.JobID= inserted.BRJobIDref
JOINProducts
ON Products.ProductID = tblJobs.ProductIDref
WHEREProducts.JobYieldRequiredIndicator = 'TRUE'
ANDinserted.Yield IN ( 0 , NULL )
)
BEGIN
RAISERROR ('Yield is required for the Product',16,1)
ROLLBACK
RETURN
END
You will also need a trigger on update of table tblJobsBR and an update trigger on table tblJobs for the case where the ProductIDref is changed to a product that requires a yield from a product that does not require yields.
SQL = Scarcely Qualifies as a Language
July 18, 2009 at 7:26 am
Thank you very much, so are you saying that I need to call this trigger while updating the tblJobsBR and tblJobs, not quite sure how to do that. Just one questions how to I get the trigger to to know which product need yields and which don't, from reading the code I think it must from a view/query I need to build called inserted which contains the list of products IDs which DO need to have a yield.
Is that correct. Jesus I have so much to learn. Thanks for you help and time taken.
Asta
CREATE TRIGGER tblJobsBR_tia_100 ON tblJobsBR AFTER INSERT
AS
SET NOCOUNT ON
IF 0 = (SELECT COUNT(*) FROM inserted) RETURN
IF EXISTS
(SELECT 1
FROM inserted
JOIN tblJobs
ON tblJobs.JobID = inserted.BRJobIDref
JOIN Products
ON Products.ProductID = tblJobs.ProductIDref
WHERE Products.JobYieldRequiredIndicator = 'TRUE'
AND inserted.Yield IN ( 0 , NULL )
)
BEGIN
RAISERROR ('Yield is required for the Product',16,1)
ROLLBACK
RETURN
END
July 19, 2009 at 8:18 am
so are you saying that I need to call this trigger while updating the tblJobsBR and tblJobs, not quite sure how to do that.
No you do not need to "CALL" the trigger as described in Books OnLine
A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view.
I think it must from a view/query I need to build called inserted which contains the list of products IDs which DO need to have a yield.
DML triggers always have access to two logical tables named "inserted" and "deleted" which have identical columns to the underlying table/view but have no indexes and have no constraints. The table named "deleted" contains the rows as they appeared before the change is made and the "inserted" table contains the rows after the change is made. For an insert statement, since the rows are being added, there are no rows in the deleted table and there are rows in the inserted table. For a delete statement, there are rows in the deleted table but no rows in the inserted. For an update statement, there are rows in both tables.
Some additional notes:
1) DML triggers execute when any valid event is fired, regardless if any table rows are affected. Hence the need to check in the trigger if there any rows are affected and usually, if no rows are affected, you will want to just exit the trigger, so the following code should be included:
IF 0 = (SELECT COUNT(*) FROM inserted) RETURN
2) The inserted and deleted tables cannot be updated.
3) For an after DML trigger, the rows in the base table have already been changed.
4) For an after DML trigger, all declared constraints have already been performed and if there is a constraint violation, SQL Server issues an error and may have performed a rollback but does not run the trigger.
5) SQL does not support a "FOR ALL" statement so alternative statements must be used. Some methods are:
a) Compare the count of rows that should meet the criteria to the count of rows that do meet the criteria and when different, then there are some rows that have invalid data.
Declare @YieldRequiredCount bigint
,@YieldProvidedCount bigint
-- Rows that should meet the criteria
SET@YieldRequiredCount
=(SELECT count(*)
FROMinserted
JOINtblJobs
ON tblJobs.JobID = inserted.BRJobIDref
JOINProducts
ON Products.ProductID = tblJobs.ProductIDref
WHEREProducts.JobYieldRequiredIndicator = 'TRUE'
)
SET@YieldProvidedCount
=(SELECT count(*)
FROMinserted
JOINtblJobs
ON tblJobs.JobID = inserted.BRJobIDref
JOINProducts
ON Products.ProductID = tblJobs.ProductIDref
WHEREProducts.JobYieldRequiredIndicator = 'TRUE'
ANDinserted.Yield > 0
ANDinserted.Yield IS NOT NULL
)
IF@YieldRequiredCount @YieldProvidedCount
BEGIN
RAISERROR ('Yield is required',16,1)
ROLLBACK
RETURN
END
That is a lot of SQL to write ! AN alternative solution is to reverse the logic from a "FOR ALL GOOD" to a "IF EXISTS ANY (NOT GOOD) ", which was the previously provided SQL that contains the where statement "inserted.Yield IN ( 0 , NULL )" , which is the "NOT GOOD" criteria.
SQL = Scarcely Qualifies as a Language
July 19, 2009 at 9:06 am
thank you for this ver helpful and informative answer. Lots of stuff there that I didn't not know but need to know.
Thanks
Asta
July 20, 2009 at 10:12 am
Just a note:
For every trigger defined on a table, SQL must run through the code of each to see what action, if any, is called for. Thus this may (or may not) become a performance concern depending on the number of these events per time. It seems like the check constraint is a sound alternative.
----------------------------------------------------
July 20, 2009 at 12:00 pm
Thank you very much, I shall keep that in mind.
Asta:-)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply