After Insert Trigger

[Kiwiman]

Hi All

This is my first real attempt at a trigger. What I am attempting to do is remove a carriage return that is included in one of the fields that is uploaded into SQL Server 2005. This syntax plays havoc with a process later on in the process - so I want to remove this immediately. We are trying to get this removed at source, but in the interim...

This appears to work, as when I run a select query on the table, I can not find any records with a carriage return - prior to this trigger being implemented there would have been, after a particular file was uploaded.

I am however unsure if this can be improved upon. Any comments on the syntax and improvements \ deficiencies would be welcome.

As always thanks for your help.

-- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE TRIGGER [dbo].[tr_Fastpay_Feed]
   ON  PROD_VAR_FEED 
AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- replace any carriage returns with an empty string

update
	t1
set
	t1.taxperiod = replace(cast(t1.taxperiod AS varchar(8000)), char(13), '')
from
	prod_var_feed t1
inner join
	inserted t2 on
		t1.id = t2.id
		and t1.interid = t2.interid
		and t1.contract = t2.contract
		and t1.payrollid = t2.payrollid
		and t1.taxperiod = t2.taxperiod
		and t1.runid = t2.runid
		and t1.source = t2.source
		and t1.feeddate = t2.feeddate
where	
	t1.source = 'FastPay'
	and (t1.taxperiod LIKE '%'+char(13)+'%')


END
GO

 

[DannySeager]

do you really need to match all the fields from T1 and T2?

Seems to me that using the ID (which is probably indexed) on it's own should be faster.

I would imagine that the speed improvment would probably be marginal but if this trigger runs a lot then a lot of little savings soon add up.

 

[Kiwiman]

Howzit

Thanks for the reply Danny.

The trigger would only be run once a day as the feeds are only loaded once a day. The ID field is indexed on its own - I just wasn't sure how it (inserted table) differentiates between three other tables which would have an Update \ Insert \ Delete trigger in them as the other tables also have the ID field as the PK.

Does the Inserted table know which table the insert data comes from?

 

[DannySeager]

The ID field in the inserted table should match the ID in T1.

The data in inserted is a match of the inserted data - I believe that the field in the inserted table will just be a long integer. (i.e. no identity auto increment)

 

[MSAccessRookie]

Howzit

Thanks for the reply Danny.

The trigger would only be run once a day as the feeds are only loaded once a day. The ID field is indexed on its own - I just wasn't sure how it (inserted table) differentiates between three other tables which would have an Update \ Insert \ Delete trigger in them as the other tables also have the ID field as the PK.

Does the Inserted table know which table the insert data comes from?

I have spent the past two weeks experimenting with this issue (Triggers on Insert to modify Columns in a Table). Along the way, I have discovered that even though an Update may be run only once per day, the Trigger (As Written) will run for every record that is Inserted in the table. For instance, if an Update cycle adds 117 records, then the Trigger will be executed 117 times. Each time, the values in the Inserted will point to the most recently Inserted record. As a result, Only a Unique ID (Single or Multiple Column) needs to be matched to assure the right record is updated.

 

[Kiwiman]

Howzit

Work beckoned yesterday, so couldn't reply. Thanks for the replies. Being new to SQL Server, I'm still trying to get my head around the Inserted table and the relationship with the table that has the trigger.

Is an "Inserted" table "created" for each table you have have a trigger against, or is there just one "Inserted" table that holds data for all tables you have the triggers against?

If I have two tables, that each have the same PK field called [ID], and both have a insert trigger, and a record is inserted into each table at exactly the same time. How does the "Inserted" table know which table each record belongs to, as both tables have a PK called ID.

Ideally I would only want to show the ID field in my inner join, not the other 7 criteria. But not quite understanding this inserted relationship...

 

[MSAccessRookie]

Howzit

Work beckoned yesterday, so couldn't reply. Thanks for the replies. Being new to SQL Server, I'm still trying to get my head around the Inserted table and the relationship with the table that has the trigger.

Is an "Inserted" table "created" for each table you have have a trigger against, or is there just one "Inserted" table that holds data for all tables you have the triggers against?

If I have two tables, that each have the same PK field called [ID], and both have a insert trigger, and a record is inserted into each table at exactly the same time. How does the "Inserted" table know which table each record belongs to, as both tables have a PK called ID.

Ideally I would only want to show the ID field in my inner join, not the other 7 criteria. But not quite understanding this inserted relationship...

My understanding (supported by observation only) has always been that the dataset INSERTED does not represent a Table Per-Se, but instead represents the Row that is being inserted to the Table. It is sort of like a Pointer in a Language like 'C++'. You can treat the dataset as if it were a Row in a Table, and any modifications will affect the Row in the particular Table that is being represented.

Each time you insert a Row into the Table that contains the Trigger, a new INSERTED dataset is created and actions will affect onty the Row being inserted until a new dataset is created to take its place. If you have two Tables that each have their own Triggers, then two different INSERTED datasets will be created (one for each Trigger), and as long as the actions of the Triggers do not interfere with each other, then they should process as expected.

Is this what you were looking for?

 

[Kiwiman]

Howzit

Perfect - that is what I am looking for. I suspected this was how it worked, like you through observation, but I could not find specific documentation on it, so taking that leap of faith - didn't really happen. I will now remove the additional criteria as they are pretty much redundant now.

Thanks very much.

 

[MSAccessRookie]

Howzit

Perfect - that is what I am looking for. I suspected this was how it worked, like you through observation, but I could not find specific documentation on it, so taking that leap of faith - didn't really happen. I will now remove the additional criteria as they are pretty much redundant now.

Thanks very much.

Glad that I could help you to understand better.

By the way, My approach appears to have been the reverse of yours. I started with minimum criteria and added what was necessary obtain the result that I was looking for. You started with a full set of criteria and are now considering scaling back. Either way, somewhere in the center is what we needed. Scale back slowly until you find your "Center", and then you should have what you are looking for. Best of luck.