locked
Trigger that updates two tables RRS feed

  • Question

  • I am using an application that contains a form, when the form is completed it updates two different tables.

    I want to write a trigger that updates a certain field when an insert or update is carried out on the table

    e.g.

    TABLE 1

    ID   Textfield1   Field1

    TABLE 2

    ID Textfield2

    Is it possible to create a trigger so that when the form is updated then Field1 can contain a concatenation of Textfield1 and Textfield2.

    Thanks

    Thursday, November 22, 2012 6:56 PM

Answers

  • Try

    create trigger trTable2_Change  ON Table2 AFTER INSERT, UPDATE
    
      AS
    
    UPDATE Table1 SET Field1 = Table1.TextField1 + I.TextField2
    
    FROM Table1 INNER JOIN Inserted I ON Table1.ID = I.ID


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Iric Wen Monday, December 3, 2012 6:43 AM
    Thursday, November 22, 2012 8:28 PM

All replies

  • Yes.

    Just INNER JOIN the inserted table with TABLE2.

    Trigger example:

    http://www.sqlusa.com/bestpractices2005/timestamptrigger/

    Warning: triggers are stealth in nature. Beginner developers be careful!


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Thursday, November 22, 2012 7:57 PM
  • Try

    create trigger trTable2_Change  ON Table2 AFTER INSERT, UPDATE
    
      AS
    
    UPDATE Table1 SET Field1 = Table1.TextField1 + I.TextField2
    
    FROM Table1 INNER JOIN Inserted I ON Table1.ID = I.ID


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Iric Wen Monday, December 3, 2012 6:43 AM
    Thursday, November 22, 2012 8:28 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions (you do not) and formatting rules. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums.

    >> I am using an application that contains a form, when the form is completed it updates two different tables. <<

    Who cares? We are Database guys and we do not care about input or presentation layers then architecture. This is Freshman Comp Sci 101 stuff!

    >> want to write a trigger that updates a certain field [sicl: fields are not columns!!] when an insert or update is carried out on the table <<

    Good SQL programmers hate triggers; they are procedural code in a declarative language. This is like cannibalism on the Food Network; possible but not desirable.

    Your whole approach to SQL is wrong. Wish you had the Netiquette to post enough that we could help you. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, November 23, 2012 3:27 AM
  • Methods

    1) on Insert/update of both tables, insert into/update field1 from the form itself without using trigger (best)

    2)or you have to write trigger for both tables but there are some issues.

    • if both Textfield1 and Textfield2 updated trigger on both table will be executed
    • in Table1 trigger, you have to do conditional update by  if not update(field1). Other wise this trigger will become infinite. update only the fields mentioned in if not update(). Since here you r calling trigger on insert/update of a table and updates the same table
    Friday, November 23, 2012 4:15 AM
  • Celko,

    Don't you worry your little cotton socks Kalman Toth, Naomi and Johnson have all helped me.

    Regards

    Fresh Man

    Saturday, November 24, 2012 10:35 AM