Microsoft SQL Constraint - limit driver from forbidden site

Question

I'm using Microsoft SQL server 2008 and I need to create a constraint that will prevent a [Tx] from being created/updated if the [Driver] is not allowed to either the source [Shipper] or destination [Consignee] site.

Tables:

Drivers (holds typical driver info)
Shipper/Consignee (source/destination site - holds locations the driver goes from/to)
DriverForbiddenSite (holds sites the driver is not allowed to)
Schedule (hold driver, and when)
Tx (a transaction has source site{shipper}, destination site {consignee}, cargo, schedule)

Here is a query that will show any record that violates the rule:

select * from DriverForbiddenSite dfs
    join Schedule sch on dfs.driverId=sch.driverId
    join Tx tx on sch.scheduleId=tx.scheduleId
    left join Shipper shi on shi.shipperId=tx.shipperId
    left join Consignee con on con.consigneeId=tx.consigneeId
where dfs.transloadLocationId=shi.transloadLocationId or dfs.transloadLocationId=con.transloadLocationId

How would be best to prevent Tx's from being created if the Driver is not allowed on the Site?

Thanks

Yeah. I am sure the question is supposed to make sense for you. — TomTom, Oct 20 '14 at 17:29
I'm sorry you don't understand the question. I need help creating a constraint. I welcome your feedback on what you don't understand? — Alan B. Dee, Oct 20 '14 at 17:33
An instead of trigger is a good idea. I'll look into that. Thanks. — Alan B. Dee, Oct 20 '14 at 18:22

score 0 · Answer 1 · answered Oct 22 '14 at 16:59

Full credit goes to Rahul. I was trying to solve this by using a constraint when instead I should use a trigger. The "instead of insert" and "instead of update" trigger will allow me to check if the driver is allowed on the Tx before it inserts/updates a Tx record.

Thanks Rahul, feel free to enter your own answer and I'll accept that instead.

Microsoft SQL Constraint - limit driver from forbidden site

1 Answers1