0

I have to create a trigger or stored procedure for an address insert. This address insert can be on multiple tables. I don't know a lot about triggers and stored procedures so I can use some help.

See here for the database schema:

Database LRS

This in Dutch so here is some translation:

  • Postcode = Postal code/zip code
  • Huisnummer = Housenumber
  • Plaatsnaam= City name
  • Straat = Street
  • Provincie = State
  • Bedrijfsnaam = Company name
  • Afdelingsnaam = Departement name

I must create a stored procedure/trigger that restricts making a insert/update into the tables ADDRESS, STREETPART and CITY. These tables can only be inserted/updated when a insert/update is made in the following tables: COMPANY, DEPARTMENT, DEPARTMENTVISITINGADDRESS, CUSTOMER or EMPLOYEE.

I also don't know how I can restrict this.

Because of the current foreign key structure (which I cannot change) there must be matching address in the ADRESS, STREETPART and CITY tables before an insert can be made in following tables: COMPANY, DEPARTMENT, DEPARTMENTVISITINGADDRESS, CUSTOMER, EMPLOYEE.

So the structure must be something like this: a user makes a insert into on the following tables: COMPANY, DEPARTMENT, DEPARTMENTVISITINGADDRESS, CUSTOMER, EMPLOYEE. But before the insert is made into one of this tables a insert must be made first into the the tables CITY (only if the city doesn't exists yet), STREETPART and ADRESS.

Firstly must be checked if the inserted postal code + housenumber is valid. I have a external postal code database where can be checked if the combination postcal code + housenumber is valid.The code below represents this:

DECLARE @POSTALCODE VARCHAR(6)
DECLARE @HOUSENUMBER INT
SELECT @POSTALCODE = POSTALCODE FROM INSERTED
SELECT @HOUSENUMBER = HOUSENUMBER FROM INSERTED

IF NOT EXISTS
(
SELECT PostalCode, MinNumber,MaxNumber
FROM PostalCode_DB.DBO.PostalCodes_NL
where @POSTALCODE = PostalCode and @HOUSENUMBER between MinNumber and MaxNumber
)
BEGIN 
RAISERROR('Invalid combination postal code and house number', 16, 1)
        ROLLBACK TRANSACTION
END

If the combination postal code + housenumber is valid there must be a check if the city is already in the CITY table. If yes, the state name and city matching the postal code must be inserted into the CITY table (the city name and state name are also in the external postal code database). After that a insert must be made into the STREETPART table (the street name mathing the postal code is also in the external postal code database). After that a insert must be made in the ADRESS table. The last step is to make a insert in the orginal insert table: COMPANY, DEPARTMENT, DEPARTMENTVISITINGADDRESS, CUSTOMER or EMPLOYEE.

Thank you for your help!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user7432713
  • 197
  • 3
  • 17
  • 4
    I can't figure out what your question is. – Gordon Linoff Apr 17 '17 at 12:19
  • 1
    This sounds like an awful lot of work to be doing in a trigger. Especially when a [foreign key constraint would be much better if you could get this data into the same database.](https://stackoverflow.com/questions/4452132/add-foreign-key-relationship-between-two-databases) Have you considered the performance implications of this? Why can't some of this work be done by an application layer? – alroc Apr 17 '17 at 13:14
  • By FAR the biggest issue here is your table structures. There is duplicated data all over the place and way over normalization in others. – Sean Lange Apr 17 '17 at 13:18
  • @GordonLinoff The question is: how can I realize this in a stored procedure? – user7432713 Apr 17 '17 at 13:41
  • @alroc This is small database. So the performance wont be any issue. This database is used on several applications, so it would be nice to realize this at the database layer. – user7432713 Apr 17 '17 at 13:43
  • @user7432713 you need to decide if you want a trigger or a stored procedure. If it's a stored procedure enforcing your referential integrity, how do you protect against someone bypassing it? – alroc Apr 17 '17 at 13:55

0 Answers0