Insert / Update Trigger based on a column's value

• G3rTrunk3n

  hi,

  triggers are like Stored proc

  except that they are automaticalluy triggered and triggers create an "Inserted" and deleted "columns"

  You can use the if staments to query the content of the inserted table.

  heres a pseudo code

   

  create trigger trigername for

  insert as

   

  declare @color varchar(20)

  select @color from inserted

  if @color='blue'

  begin

  --- do insert to table 2

  end

   

  if @color='red'

  begin

  --- do insert to table 3

  end

   

  Using the inserted and deleted Tables

  Two special tables are used in trigger statements: the deleted table and the inserted table. MicrosoftR SQL Server 2000 automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for trigger actions; however, you cannot alter the data in the tables directly.

  The inserted and deleted tables are used primarily in triggers to:

  • Extend referential integrity between tables.
    
    
  • Insert or update data in base tables underlying a view.
    
    
  • Check for errors and take action based on the error.
    
    
  • Find the difference between the state of a table before and after a data modification and take action(s) based on that difference.

  The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.

  The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added simultaneously to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.

  An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.

  When you set trigger conditions, use the inserted and deleted tables appropriately for the action that fired the trigger. Although referencing the deleted table while testing an INSERT, or the inserted table while testing a DELETE does not cause any errors, these trigger test tables do not contain any rows in these cases.

  Note  If trigger actions depend on the number of rows a data modification effects, use tests (such as an examination of @@ROWCOUNT) for multirow data modifications (an INSERT, DELETE, or UPDATE based on a SELECT statement), and take appropriate actions.

  SQL Server 2000 does not allow text, ntext, or image column references in the inserted and deleted tables for AFTER triggers; however, these column references are allowed for INSTEAD OF triggers. For more information, see CREATE TRIGGER.

  Using the inserted and deleted Tables in INSTEAD OF Triggers

  The inserted and deleted tables passed to INSTEAD OF triggers defined on tables follow the same rules as the inserted and deleted tables passed to AFTER triggers. The format of the inserted and deleted tables is the same as the format of the table on which the INSTEAD OF trigger is defined. Each column in the inserted and deleted tables maps directly to a column in the base table.

  The rules regarding when an INSERT or UPDATE statement referencing a table with an INSTEAD OF trigger must supply values for columns are the same as if the table did not have an INSTEAD OF trigger:

  • Values cannot be specified for computed columns or columns with a timestamp data type.
    
    
  • Values cannot be specified for columns with an IDENTITY property, unless IDENTITY_INSERT is ON for that table. When IDENTITY_INSERT is ON, INSERT statements must supply a value.
    
    
  • INSERT statements must supply values for all NOT NULL columns that do not have DEFAULT constraints.
    
    
  • For any columns except computed, identity, or timestamp columns, values are optional for any column that allows nulls, or any NOT NULL column that has a DEFAULT definition.

  When an INSERT, UPDATE, or DELETE statement references a view that has an INSTEAD OF trigger, the database engine calls the trigger instead of taking any direct action against any table. The trigger must use the information presented in the inserted and deleted tables to build any statements needed to implement the requested action in the base tables even when the format of the information in the inserted and deleted tables built for the view is different than the format of the data in the base tables.

  The format of the inserted and deleted tables passed to an INSTEAD OF trigger defined on a view matches the select list of the SELECT statement defined for the view. For example:

  CREATE VIEW EmployeeNames (EmployeeID, LName, FName)
  AS
  SELECT EmployeeID, LastName, FirstName
  FROM Northwind.dbo.Employees
  

  The result set for this view has three columns: an int column and two nvarchar columns. The inserted and deleted tables passed to an INSTEAD OF trigger defined on the view also have an int column named EmployeeID, an nvarchar column named LName, and an nvarchar column named FName.

  The select list of a view can also contain expressions that do not map directly to a single base table column. Some view expressions, such as a constant or function invocation, may not reference any columns and can be ignored. Complex expressions can reference multiple columns, yet the inserted and deleted tables have only one value for each inserted row. The same issues apply to simple expressions in a view if they reference a computed column that has a complex expression. An INSTEAD OF trigger on the view must handle these types of expressions. For more information, see Expressions and Computed Columns in INSTEAD OF Triggers on Views.

  
  
  

• SavitaG

  How many informations do you plan to store in the aditional tables

  If its "just" one field you could think about a table that has a propertyName + Value so it would allow you to keep the number of tables smaller. Also when someone decides that you also have to add another color it will be possible without the change of any existing code. But that would require that only very few (and similar) Datatypes are stored in that table...

  
  
  

• RobertW

  Thanks for everyone’s input.

  My apologies for lack of a better description of what I was trying to do, I simply tried to do this in haste and I should have slowed down and made a better / clearer example.

  The reason I was looking at this on a trigger level is that ‘it’ (the solution) would have to be able handle individual updates as well as batch updates through a dataset from a DataGrid (for example).

  This is basically…
  Orders to OrdersDetails

  Table1 [Orders] (New Row)
  Column2 ‘Male’

  …Inserts a New Row only into…
  Table2 [OrderDetails_Men]

  Table1 [Orders] (New Row)
  Column2 ‘Female’

  …Inserts a New Row only into…
  Table3 [OrderDetails_Women]

  When a new row is inserted into [Orders] and the value in that new row’s Column2 has the value of ‘Male’, a new row is also inserted into [OrdersDetails_Men]. [OrdersDetails_Men] is related to the value ‘Male’ in [Orders]. An example would be that [OrdersDetails_Men] lists different shoe sizes available for men’s stock on hand at that time and could include styles, colors etc.

  The same applies to [Orders] and [OrdersDetails_Women], when having the value of 'Female' in the newly created row in [Orders].
  Here it could list heel height, straps, open toe etc.

  The trigger would also have to trap for changing the record later, where someone put the wrong 'gender' down and had the wrong shoe / type listed.

  So when going back and changing the gender, the incorrect [OrderDetails...] record would have to be deleted, and the correct new [OrderDetails…] would be inserted.

  What would this example trigger look like... and would this be the best way to approach this using datasets
  

  ----Edit-------
  This example was just for a hopefully better explanation of what I was trying to do.
  In real life I guess if this was an actual Order - OrderDetails, I wouldn't delete the original input, just ammend it, and create a new one.
  But for my purposes I do not need to keep an on-going record, just delete and create related records based on a 'value' in a column. I have never done this with a trigger before (I will have to study up on joeydj's post). Thanks.
  Hatzi... I finally got what you mentioned... <g>  ;)
  Oooo...Very Good point, thats another way to do it, but I feel I couldn't use it here.
  There would literally be thousands of records and I think I would also have to trim, convert etc. and soon be beyond my skills. Plus additional columns. Thanks for the idea anyway.
  Louis... if you have any other comments, examples, please do so... Thanks.
  
  

• Guido Pica MSFT

  Can you post a more complete schema along with some data So far I cannot seem to follow what you want to do, but you should be able to do most anything with triggers you want.
  
  

• Lokka

  Hello...

   

  Its not quite clear what you want.  Do you allways want to insert the data into table 1 or not

  What you should look at is a trigger for Inerts and updates. When you create a trigger on a table you will have access to 2 special tables... They are "inserted" and "deleted". Those tables will contain all rows that where inserted/deleted in an update/insert. An update will fill BOTH of those tables and an insert will only fill the inserted table.

  So to handle inserts you can just select from the inserted table and insert those rows in the appropiate table. Make sure you do it with a "select ... into" so you will be able to handle multi line inserts. The update can just delete all records from the "deleted" table in tab tables table2,table3 etc... then insert the appropiate information into the other tables.

  Now the "why" question ;)

  Why do you need to duplicate this information Duplication of data is allways a bad thing in a relational DB, and it WILL cause some problems later on. Wouldnt it be easier to create a view that would only output the IDs of all "red" or "blue" records It would also allow you to skip all the deleting and inserting of records. Also triggers should be used as sparringly as possible in my oppinion, and a view with a index on "Color" and "ID" of table one would provide you with great performance.

   

  [edit] Warning about Post #2... The posted pseudo code will cause problems with multi line updates/Inserts... So it wont work if you decide to update ALL red rows to blue rows. Allways make sure your trigger works with more then 1 line of changed data

  
  
  

• steve64

  Hello,

  Thanks for your replies and the added information.
  Maybe with a little more information I can communicate better to what I am trying to do.
  
  Table1 is the main table (or one of the tables that form relationships) from which I have multiple related tables linked to.
  
  When a new row is inserted into table1, new rows are inserted into numerous other tables that are *attributes* of the item in table1 which help define the primary key for table1. So all I am doing is defining the attributes of table1 with the newly created rows in the related tables.

  Column2 in table1, lets just say ‘Color’ for right now, only has two choices (this could have been a true/false statement – there will be only two choices, never any more).
  If ‘Blue’ was chosen, another row was created in a related table which lists attributes or further defines (options) of ‘Blue’ in table2.
  The same goes for ‘Red’.
  There are no records that are being duplicated.
  If ‘Blue’ was chosen, table2 would have spawned; thickness for the related ID in the new row.
  If ‘Red’ was chosen, table3 would have spawned; weight for the related ID in the new row.
  Strictly one or the other.
  Sorry… color was a bad choice here…
  Later down the road there are options to edit those attributes for their color (either thickness or weight; this is the problem part – and it is absolutely necessary to edit the columns of thickness or weight), so if someone went back and changed the color, I would have to delete that color records attributes in the related table and then insert the new row into the correct related table with the new attribute (to help eliminate orphaned records).
  I also had to make ‘thickness’ and ‘weight’ attributes… coming from a user defined look-up table for those related tables. So the user could assign their own values for those individual attributes, which blew out inserting Default Values because that would come from a user defined related table which I wouldn’t know what value(s) was/were actually in there.
  I thought I would need another table – table1Color; which would support more colors.
  But even if I created that one, there would still be conditional statements when the value changed for the color column.
  So I am back to conditional statements to insert new rows in related tables.
  You might say that I am trying to make a relationship bound to a value in a column…
  Make sense

  Should I basically keep the conditional statements out of the trigger scenario and handle it at the form level

  Thanks.

  Edit----------

  You know a better scenario would have been Male, Female.
  Where certain things would only apply to each... the idea is the same.
  

  
  

• virnoche

  I am not quite sure what you are talking about.
  
  There is more than one attribute for each 'color', whether Blue or Red was chosen.
  Creating another table that lists color and properties would not work even if there was only one attribute or property for each color.
  Regardless, there is more than one property per 'color'; that is why I needed to create a new row for each table.
  
  
  
• Insert / Update Trigger based on a column's value