Should I use trigger here?

[jtrembla]

I have a table (Lets call it Table1) in which about 80% of the records are created from a storage table (by rolling/grouping up records), and there is no direct relationships to it since the storage table comes from a read only warehouse and its ID's can change if the readonly warehouse is rebuild from the datasource. In addition, the users can add records to table1 that are not in the warehouse.

Now I have another table (lets call it table2) that does have a relationship to table 1, but also other tables.

My problem is this... when a record is added to table 1, I need to add records to table 2 based on some fields criteria from table 1.

I was thinking of using a trigger for this, since there are 2 ways of adding data to table 1, (via the following transaction and per record basis.

FYI
PricingDrugCurrentTherapyDosing is the storage table -

PricingDrugGroup is 'mostly created by records in PricingDrugCurrentTherapyDosing table (Table1)

			--Deletes records FROM dbo.PricingDrugGroup That are NOT IN the 
			--PricingDrugCurrentTherapyDosing table at the GeographyID,PricingDrugID,BrandGeneric Grouping Level
            DELETE  From dbo.PricingDrugGroup
            where   Exists ( SELECT dbo.PricingDrugGroup.GeographyID,
                                    dbo.PricingDrugGroup.PricingDrugID,
                                    dbo.PricingDrugGroup.BrandGeneric,
                                    1 AS PricingDrugGroupTypeID
                             FROM   dbo.PricingDrugGroup
                                    INNER JOIN dbo.PricingDrug ON dbo.PricingDrugGroup.PricingDrugID = dbo.PricingDrug.PricingDrugID
                             WHERE  dbo.PricingDrug.VersionID = @VersionID
                                    AND dbo.PricingDrugGroup.PricingDrugGroupTypeID = 1
                             EXCEPT
                             SELECT dbo.Geography.GeographyID,
                                    dbo.PricingDrugCurrentTherapyDosing.PricingDrugID,
                                    dbo.PricingDrugCurrentTherapyDosing.BrandGeneric,
                                    1 AS PricingDrugGroupTypeID
                             FROM   dbo.Geography
                                    INNER JOIN ( dbo.PricingDrug
                                                 INNER JOIN dbo.PricingDrugCurrentTherapyDosing ON dbo.PricingDrug.PricingDrugID = dbo.PricingDrugCurrentTherapyDosing.PricingDrugID
                                               ) ON dbo.Geography.GeographyName = dbo.PricingDrugCurrentTherapyDosing.Country
                             WHERE  ( (( dbo.PricingDrug.VersionID ) = @VersionID) )
                             GROUP BY dbo.Geography.GeographyID,
                                    dbo.PricingDrugCurrentTherapyDosing.PricingDrugID,
                                    dbo.PricingDrugCurrentTherapyDosing.BrandGeneric )

			--This adds records into PricingDrugGroup that does not have matching records in 
			--PricingDrugCurrentTherapyDosing at the GeographyID,PricingDrugID,BrandGeneric Grouping Level
            INSERT  INTO dbo.PricingDrugGroup
                    (
                      GeographyID,
                      PricingDrugID,
                      BrandGeneric,
                      PricingDrugGroupTypeID
                    )
                    SELECT  dbo.Geography.GeographyID,
                            dbo.PricingDrugCurrentTherapyDosing.PricingDrugID,
                            dbo.PricingDrugCurrentTherapyDosing.BrandGeneric,
                            1 AS PricingDrugGroupTypeID
                    FROM    dbo.Geography
                            INNER JOIN ( dbo.PricingDrug
                                         INNER JOIN dbo.PricingDrugCurrentTherapyDosing ON dbo.PricingDrug.PricingDrugID = dbo.PricingDrugCurrentTherapyDosing.PricingDrugID
                                       ) ON dbo.Geography.GeographyName = dbo.PricingDrugCurrentTherapyDosing.Country
                    WHERE   ( (( dbo.PricingDrug.VersionID ) = @VersionID) )
                    GROUP BY dbo.Geography.GeographyID,
                            dbo.PricingDrugCurrentTherapyDosing.PricingDrugID,
                            dbo.PricingDrugCurrentTherapyDosing.BrandGeneric
                    EXCEPT
                    SELECT  dbo.PricingDrugGroup.GeographyID,
                            dbo.PricingDrugGroup.PricingDrugID,
                            dbo.PricingDrugGroup.BrandGeneric,
                            1 AS PricingDrugGroupTypeID
                    FROM    dbo.PricingDrugGroup
                            INNER JOIN dbo.PricingDrug ON dbo.PricingDrugGroup.PricingDrugID = dbo.PricingDrug.PricingDrugID
                    WHERE   dbo.PricingDrug.VersionID = @VersionID
                            AND dbo.PricingDrugGroup.PricingDrugGroupTypeID = 1

I figured I can use a trigger for this since I can keep all the logic for inserting into table 2 in one place.

Unless there is a way I can insert a call to a stored proc in the above transaction that will get called on a per record insert??

 

[RiverGuy]

If you load Table1 en masse from the warehouse, triggers could impact the load performance by quite a bit.

But to be honest, I would suggest a different strategy altogether. You've got user generated transactions mixed with historical/summary data, and an additional table to view the data another way from the looks of it.

I'd create a table/tables/schema/whatever you need/etc. for the transactional records. Put a trigger on the main transactional table to update the LastTimeModified.

Then, create an ETL process to load your Table1 and Table2 with your dw-read-only table and your new transactional table as sources.

 

[jtrembla]

Let me explain more clearly...

Our objective is to forecast the drug market, current drugs as well as emerging drugs that will be added to the market during the forecast time line.



The storage table PricingDrugCurrentTherapyDosing holds mappings of drug data from the warehouse to user defined group name, essentially current drugs already on the market. Dosing assumptions are added by the user and then this table is used to create the pricing drug group records (into the pricingdruggroup table) via the above transaction - see code.

These groups are flagged with a type of "Current Therapy Drugs".

Users then can add groups of other types, i.e. "Emerging Drugs" (drugs that will be out in the market during the forecast time line).

Since our forecast are essentially done via this level I assumed it would be easier to keep it all in the same table and just handle the two different loading methods.

FYI,
A PricingDrugGroup consists of
PricingDrugGroupID
PricingDrugID (essentially the user defined name of the group)
BrandGeneric (whether this item is a branded or generic drug(
CountryID (the location of the drug)








Users can then add into the pricingdruggroup table. The reason I kept both transaction types in the same table is that it makes the rest of the application a little simpler. This PricingDrugGroup table is the level at which user forecasts are taking place.

 

[RiverGuy]

Let me know if I understand this correctly. These are the tables you have?

1. A read-only data warehouse table
2. An intermediate, "mapping" table of which accepts user updates.
3. A summary table, built off of #1 and #2, with the ability for users to add their own records as well.
4. A second summary table, built off of #3, with the ability for users to add their own records as well.

I'm not sure how many records you are talking about, but assuming at least a moderate number of records--in the thousands maybe--with 20% of those as user generated records--you're not talking about a user keying in a couple of records per week. It sounds like a fairly consistent transaction volume.

To me, it still seems like a better approach to separate the user generated data and the imported data unless the data volume is really small. You usually don't find much data entry in the final presentation tables of a Data Mart (which is partially what you are creating). This also makes it easier if you need to truncate and reload the entire final destination--you won't have to save off or rekey any data.

But yes, you could keep it the way you have it and use a trigger. I usually like to keep triggers small and fast. Yours might not be either (fast meaning having virtually no end user impact on performance), but I've seen a lot worse.

But if you go the trigger route, you don't want to just copy and paste that query in. Instead of deleting and reloading a bunch of records for each one inserted, you can create separate DELETE/INSERT/UPDATE triggers and use the INSERTED/DELETED tables within the scope of the trigger to affect just the records you need to affect in your target table.

 

[SQLSister]

And rememebr you can only use a trigger in this case if the second insert can be done completely from data avalaible from the first insert and system variables. So if table two has a required field that contains values that you can figure out from the first insert, then you can't use a trigger. That looks as if this isn't the case here, but I thought I'd throw it out to save you (or some future reader) some development time, if this was the case.

"NOTHING is more important in a database than integrity." ESquared

 

[jtrembla]

Here are my tables

1. A read-only data warehouse table
2. An intermediate, "mapping" table of which accepts user updates.
3. A summary table, built off of #1 and #2, with the ability for users to add their own records as well.

The rest of the tables are relational to #3.

Keep in mind, table 3 isn't a true summary table, it is essentially the skeleton for the forecast. It just happens to be built via 2 different transactions. I can separate the skeleton in two tables and union the results at the end, but then it will get complicated once I add dependencies/events in a forecast since I will be dealing with cross entity dependencies. (i.e. a pricing group will lose 10% from this pricing group along time-points A and B)

I think I can still do this without triggers by just using the new EXCEPT functionality in SQl 2005 to the en masse transction when adding to table 3 from the mapping table and just call a simple insert when dealing with user inserts? What do ya think?