SQL insert which updates one table and based on a field value chooses what other table needs updating

inserttable

DB setup: MSSQL

I have a source record coming in via API call. That call is intended to update a system in the database.

The first table is a common table where all systems have some details

SystemId   | SystemName   | Type      | Memory    | Storage  | Etc......
-----------+--------------+-----------+-----------+-----------
92         | Server1      | BlueType  | 16GB      | 10TB     | 
96         | Server2      | RedType   | 24GB      | 20TB     | 
...
...

Then I have at least 2 tables, each with different fields pertaining to a particular role of the system:

BlueId  | BrokerName   | Threshold | TriggerLevel  | Etc......
--------+--------------+-----------+---------------+-----------
41      | Palooza      | 400       | 4             |  
42      | Almamater    | 900       | 3             |  
...
...

RedId   | Parent      | MetalCast | Available  | Etc......
--------+-------------+-----------+------------+-----------
87      | Pindle      | Yes       | Yes        |  
88      | Feldspar    | No        | No         |  
...
...

The source that is providing the data would be submitting it in one statement. That statement would come from a source that knows what "type" it is (red, blue, etc) and would have the appropriate fields with it as it pertains to that type.

For example, if the statement is coming from a "Red" type of system, the statement would come in similar to the following:

SystemName = CrosserTT1
Type = BlueType
Memory = 20GB
Storage = 10TB
BrokerName = PintoCall
Threshold = 250
TriggerLevel = 6
etc....

This means that I need to take this data, update the "Common" table as above with the appropriate fields, determine what the value of "Type" is and based on the type value update the appropriate table with the appropriate fields. In this example above, I'd want to update the common table, and then update the Blue table based on the value of the type. I wouldn't want to even touch the red table in this case.

These are the things I'm wondering:

  • Is this possible to have an insert statement that in-one-shot can update common, choose which type table to update, then go update that?
  • Should I have a single table that contains all the fields of all the types and only add data into the columns appropriate to the type?
  • Should I have a single table INCLUDING COMMON that has all fields of everything system-related and leave null the fields that don't pertain to a record?

I'm trying to determine the best practice is for this kind of thing – for performance and simplicity.

In terms of the insert statement with the example above, would it make more sense to have a Stored Procedure and provide it all the values instead?

Thanks for your valued input.

If you need clarification, please let me know.

Best Answer

What you are looking for is called Trigger.

For example:

https://sqlite.org/lang_createtrigger.html

I've wrote a little sample for sqlite, you could repeat everything on any unix/linux system.

The init.sh:

# cat init.sh 

echo "CREATE TABLE 'first_table' ( id integer primary key, \
                        name            text DEFAULT 'noname', \
                        type            text DEFAULT 'blue', \
                        blabla          int  DEFAULT 0 \
                        );"

echo "CREATE TABLE 'blue_table' ( id integer primary key, \
                        name            text DEFAULT 'noname', \
                        blabla          int  DEFAULT 1 \
                        );"

echo "CREATE TABLE 'red_table' ( id integer primary key, \
                        name            text DEFAULT 'noname', \
                        blabla          int  DEFAULT 2 \
                        );"

echo "CREATE TRIGGER t1 AFTER INSERT ON first_table \
        FOR EACH ROW \
                WHEN NEW.type != 'blue' \
                BEGIN \
                        INSERT INTO blue_table ( name ) VALUES ( NEW.name ); \
                END;"

echo "CREATE TRIGGER t2 AFTER INSERT ON first_table \
        FOR EACH ROW \
                WHEN NEW.type != 'red' \
                BEGIN \
                        INSERT INTO red_table ( name ) VALUES ( NEW.name ); \
                END;"

echo "INSERT INTO first_table ( name, type ) VALUES ( 'ololo1', 'red' ), ( 'ololo2', 'blue' ), ( 'ololo3', 'blue' ), ( 'ololo4', 'red' );"

Firstly, here, you are create 3 tables, thereafter 2 triggers, which are fires according to type field, if type is 'red' - fired 1st trigger, if type is 'blue' - another one.

Usage:

# bash init.sh | sqlite3 tmp.db
# echo "SELECT * FROM red_table;" | sqlite3 tmp.db 
1|ololo2|2
2|ololo3|2
# echo "SELECT * FROM blue_table;" | sqlite3 tmp.db 
1|ololo1|1
2|ololo4|1
Related Question