Triggers

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

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/

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?

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

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.

Can you give me an example for multiple fields?

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 />

Can anyone explain what is wrong with that piece of code?

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).