SQL Server Performance

Triggers

Discussion in 'SQL Server 2005 General Developer Questions' started by ralphhome, Jul 7, 2007.

  1. ralphhome New Member

    Hello,

    I want to write a trigger which does the following

    When a record is inserted in a table,

    I want to check a field from the new record on a specific value

    declare @TypeTrans number ,
    if <fieldname> = 'SAL'
    @TypeTrans = 1
    endif

    if <fieldname> = 'PUR'
    @TypeTrans = 2
    endif

    then insert this

    insert into TransOrder (TypeTrans) VALUES ( @TypeTrans )

    Can someone give me a sample of how I can write this trigger.

    Thanks in advance

    Ralph
  2. ndinakar Member

    Is there any reason why you want to do this in a Trigger only? How do you normally INSERT into the table?? via procs or via In-line SQL? If your INSERTs are via procs, I'd recommend modifying the proc to check for the value and do the appropriate INSERT into the TransOrder table within the proc itself. Triggers have a lot of maintenance headache and have to be used very cautiously. Do a quick google search and you will find plenty of articles on the pros and cons of using triggers and then you can decide for yourself.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  3. Adriaan New Member

    The previous posting contains good advice.

    If you decide to do it in a trigger, try to change your mindset to set-based logic. Do not move values from columns to variables to columns, but use T-SQL in a more to-the-point way.

    CREATE TRIGGER MyTrigger ON dbo.MyTable
    FOR INSERT
    AS

    IF EXISTS (SELECT * FROM inserted WHERE inserted.fieldname = 'SAL' OR inserted.fieldname = 'PUR')
    BEGIN

    INSERT INTO dbo.TransOrder (TypeTrans)
    SELECT CASE inserted.fieldname WHEN 'SAL' THEN 1 WHEN 'PUR' THEN 2 END
    FROM inserted
    WHERE inserted.fieldname = 'SAL' OR inserted.fieldname = 'PUR'

    END

    I presume this won't be in a production database, or that you will be copying more data into the TransOrder table?
  4. ralphhome New Member

    hello,

    Thx for the replies;

    What is the reason : a process which I don't control (remote process) adds records to a certain table fe TransactionList.

    My application reads all transaction details from another table, from which I make reports.

    The reason I would have like to use a trigger is when that remote process add a record I automatically add a record to my table.

    In the base table TransactionList (TL) there is a field which can contain :
    SAL, TRF+, TRF- . But in my table this is done a bit different :
    Order = 1, Sal = 2, TRF- = 3, TRF+ = 4

    In the trigger I would like to compare that and write this in the TypeTrans field as mentioned below.

    and insert this in my table which contains more than one field.

    insert into TransLine(Typetrans, QTY, StoreID, DateCreate) VALUES ( <1,2,3,4>, TL.QTY,
    TL.SPID, TL.DateCreate)

    Thanks

    Ralph
  5. Adriaan New Member

    Use the syntax I gave you. inserted is a snapshot of all the rows that were inserted. Inside the trigger, you can query this like you would any other table. It contains all columns of the actual table, except TEXT and other 'big' data types.

    My example also gives you the set-based approach - use that instead of a VALUE list.
  6. ralphhome New Member

    Can you give me an example for multiple fields?
  7. dineshasanka Moderator

    Select @var1 = Field1,@var2 = Field2 FROM Inserted

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  8. ralphhome New Member

    CREATE TRIGGER trg_Insert ON [dbo].[tblproduct_stock_history] <br />FOR INSERT<br />AS<br />declare @TypeTrans varchar(9) ,<br />@SPID numeric ,<br />@QTY numeric ,<br />@ARTcode varchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' />,<br />@ARTID numeric<br /><br />IF EXISTS (SELECT * FROM inserted )<br />BEGIN<br /><br />Select @Artcode = inserted.prod_id FROM Inserted<br /><br />Select @ARTID = Select ID from dbo.tblProduct where Code= @Artcode<br /><br />INSERT INTO dbo.TblStockmov (Artid, ActionCode, QTY,SPID, DCREA)<br /><br />SELECT @ArtiD, CASE inserted.type WHEN 'PUR' THEN 2 WHEN 'SAL' THEN 2 END, inserted.qty1,inserted.pos_id, inserted.datetime<br />FROM inserted<br /><br /><br />END<br />
  9. ralphhome New Member

    Can anyone explain what is wrong with that piece of code?
  10. dineshasanka Moderator

    Select @ARTID = ID from dbo.tblProduct where Code= @Artcode

    Remove second select

    Also You don't need have to have

    IF EXISTS (SELECT * FROM inserted )

    I can't undestand what is purpoe of the last select statment



    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  11. Adriaan New Member

    Dinesh,<br /><br />My advice was to avoid using variables, by just using an INSERT based on a SELECT from the snapshot.<br /><br />To the original poster:<br /><br />INSERT INTO target (&lt;column_list&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /><br />VALUES (&lt;value_list&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /><br /><br />... which is fine for one set of values, but if you (may) need to handle multiple rows, don't bother copying data into variables (that's what programmers do - database developers know better!) --- just use this:<br /><br />INSERT INTO target (&lt;column_list&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /><br />SELECT &lt;column_list&gt;<br />FROM &lt;table_or_joined_tables&gt;<br /><br />In a trigger, you have the special 'snapshots' called <b>inserted</b> and <b>deleted</b> which you can use in the SELECT and FROM part of an INSERT query (within a trigger).

Share This Page