Trigger that checks other tables
-
Hi, I don't really have much time for self learning right now so I would appreciate if someone writes here a trigger that does this things, I'm new to trigger commands so I don't know how to do it.
TRIGGER 1: i have 3 tables, all of them describe a place. I want to prevent creating 2 "Warehouses" in the same place but I have to check in 2 external tables.
INPUT FIELDS: IdPlace (bigint), Warehouse (bit)
before update and insert commands are executed for table 'dbo.ClientPlaces'
if field 'Warehouse' of the input equals 1
if there is (exists) a record with the same 'IdPlace' as the input and with value 1 in 'Warehouse' in the tables 'dbo.AgentPlaces' and 'dbo.RepresentantPlaces'
return an error message 'There is already a warehouse at this place'
else
do the insert or update
end if
else
do the insert or update
end if
-
Sorry but we need more info to answer that. Can you post the tables definition (ddl + keys/relations) so that we can built it up?
-
my apologies for obsessing over spellings since that is not an issue here...but could you please change wharehouse to warehouse ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
It's a good thing that the "o" and the "a" are far apart on the keyboard
. -
The only things you would need to know is this:
TABLE: dbo.Places
FIELDS:
IdPlace [bigint, Primary Key]
RELATIONSHIPS: dbo.ClientPlaces.Place, dbo.ProviderPlaces.Place, dbo.AgentPlaces
TABLE: dbo.ClientPlaces (use same definition and relationships for ProviderPlaces and AgentPlaces, just changing the word Client to Provider or Agent)
FIELDS:
Client [bigint]
RELATIONSHIPS: dbo.Clients.IdClient
Place [bigint]
RELATIONSHIPS: dbo.Place.IdPlace
Warehouse [bit]
RELATIONSIPS: none
TABLE: dbo.Clients (use same definition and relationships for Providers and Agents, just changing the word Client to Provider or Agent)
FIELDS:
IdClient [bigint, Primary Key]
RELATIONSHIPS: dbo.ClientPlaces.Client
* Obviously there are more fields in Client, Provider, Agent and Place tables, but they are not needed for this trigger. And sorry for the spelling, I'm not native speaker!.
-
DDL = the create script (create table...)
Use EM to generate those, I don't mind about the extra columns.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply