TRIGGER to insert multiple entries based on the insert of one

[cambridgesteve]

Is it possible to have a TRIGGER on a table such that when an INSERT is made into it - it INSERTs another 3 entries for example ?

Let's suppose we have a table company_settings and if we INSERT an entry for one company it INSERTS for another (associated) 3 companies.
If the table is structured:
[companyid] INT
[settingcode] INT
... and we INSERT (3, 10) it also then TRIGGERS and INSERTS (4, 10), (5, 10) and (6, 10) for example.

I'm unsure whether something like this is possible (and happy to throw in the towel if it's not)...

Any help would be appreciated.

Thanks.
Steve

 

[gmmastros]

This is certainly possible. You need to be really careful about working with triggers because it could really slow down your database, or worse, it could cause data problems.

However, when done correctly, triggers work well and can really help your data by making sure that it is always correct (as long as you are careful enough).

To be careful, it's important that you document all of the "rules" for the trigger so that we can give you the best advice possible. For example....


1. If the setting code is changed for companyid 3, should it also change for the other company id's?

2. If a setting code is changed for companyid 4,5, or 6, should the settingcode change for the other companyid's in the set?

3. Is there something special about companyid 3 that requires 3 extra rows where other companyid's would NOT get the 3 extra rows?

4. What is the desired behavior when a company is deleted?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

To add to George's comments...how will you determine the associated companies? Are you going to hard-code it - so that if an insert is done for company three, there will be inserts for each associated company? Or do you have a table with associations?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[cambridgesteve]

SQLBill - I'll have a table with associations.

George - yes. I'll be looking for similar interactions for DELETEs and UPDATEs. The question about INSERTS is just to keep things simple to start with.

Thanks.
Steve