(Resolved)Easy one on Triggers (hopefully)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

(Resolved)Easy one on Triggers (hopefully)

Author

Topic

jrogers
Starting Member

34 Posts

Posted - 2008-10-08 : 10:14:51

I have created a trigger as shown below

CREATE TRIGGER [dbo].[tr_Trade_Insert] 
   ON  [dbo].[Trade] 
   FOR INSERT
AS 

if exists (Select i.Execution_Id from inserted i inner join trade t on i.execution_id = t.execution_id)

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
	UPDATE Trade set Execution_status = '2' 
	from trade t inner join  inserted i on t.execution_id=i.execution_id

END

What I am trying to achieve is when there is a new entry to the table I want to check if the execution_id already exists - if so I want the execution_status changed to '2' and then insert the new record

I am inserting each record from a vb application.

At the moment it seems to update all records with the same execution_id to have an execution_status ='2' . I want the trades in inserted table (which I beileve is a temp table waiting to be inserted) to be inserted unnaffected

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 10:19:47

Ok its looks fine. Now what is your question?

jrogers
Starting Member

34 Posts

Posted - 2008-10-08 : 10:24:07

Sorry I posted before I finnished my question:-(

jrogers
Starting Member

34 Posts

Posted - 2008-10-08 : 10:24:56

also this is SQL 2005 express

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 10:27:22

whats the primary key of your table?

jrogers
Starting Member

34 Posts

Posted - 2008-10-08 : 10:37:35

That is the problem.. There isn't a primary key. I know it's really bad design, and thats not the half of it.

The table has 325 columns, and each day about 400 rows get added. The reason for this is I have to utalise an API to get data out from another system - The data is presented to me like this.

My issue with the primary key is what to base it on.. I know that execution _ID is my main reference.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 10:40:34

In that case make the trigger instead of and try like this

CREATE TRIGGER [dbo].[tr_Trade_Insert] 
   ON  [dbo].[Trade] 
   INSTEAD OF INSERT
AS 
BEGIN
if exists (Select 1 from inserted i inner join trade t on i.execution_id = t.execution_id)

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
	UPDATE Trade set Execution_status = '2' 
	from trade t inner join  inserted i 
        on  t.execution_id=i.execution_id

END
INSERT INTO Trade
SELECT * FROM INSERTED
END

jrogers
Starting Member

34 Posts

Posted - 2008-10-08 : 10:49:37

thanks very much I will give it a go.. Do you mind explaining a couple of things

What is the difference between INSTEAD OF, FOR and AFTER triggers, and why would you use one over another.
and also why do I need to add the

INSERT into Trade
SELECT * FROM INSERTED

does this not automatically get inserted?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 10:53:31

quote:
Originally posted by jrogers

thanks very much I will give it a go.. Do you mind explaining a couple of things

What is the difference between INSTEAD OF, FOR and AFTER triggers, and why would you use one over another.
and also why do I need to add the
INSERT into Trade
SELECT * FROM INSERTED

does this not automatically get inserted?

INSTEAD OF trigger fires before the prescribed action. so its used in cases where we want to do some logic before action and then proceed accordingly (either do action or go for some other action). FOR & AFTER are same. they are fired only after the event (INSERT,UPDATE,DELETE) and are use for doing consequent processing of data.
you can get detailed explanation for each in books online.

jrogers
Starting Member

34 Posts

Posted - 2008-10-08 : 11:50:20

thanks.. I have looked on BOL breifly, but I was looking for a diference between FOR/AFTER and could not see any mention of FOR, but noticed when searching on Google there does not seem to be any differentiation between FOR adn AFTER.. are they exactly the same? why don't ms jsut scrap one of them if this is the case?

jrogers
Starting Member

34 Posts

Posted - 2008-10-08 : 12:48:48

Adding this trigger has been a killer on performance, what do you think I could do to improve performance?

I have a non-clustered index on Execution_ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 12:52:59

quote:
Originally posted by jrogers

thanks.. I have looked on BOL breifly, but I was looking for a diference between FOR/AFTER and could not see any mention of FOR, but noticed when searching on Google there does not seem to be any differentiation between FOR adn AFTER.. are they exactly the same? why don't ms jsut scrap one of them if this is the case?

FOR & AFTER are one & same. FOR is included for backward compatibility.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 12:55:10

quote:
Originally posted by jrogers

Adding this trigger has been a killer on performance, what do you think I could do to improve performance?

I have a non-clustered index on Execution_ID

Definitely presence of trigger will have an impact on performance.
If you dont want trigger, then do the processing in your insert procedure itself. do you have audit column like datecreated in your table?

jrogers
Starting Member

34 Posts

Posted - 2008-10-08 : 13:15:59

I have Execution_Date which is the date on which the trade happened, then there is a column called Execution_update_date which is the date on which the execution was updated..

so If I enter a trade today for todays date, then both those numbers will be the same (and the Excution_status will be 0)
If I amend the trade tomorrow, the execution_date will still be the same, but the Execution_update_date will be todays date.

But If I enter a trade today for yesterdays date (and this is the first time the trade has been inserted) then execution_date will be yesterday and execution_update_date will be today, but as there is not already a record in the database for this trade, no updating needs to happen.

I was thinking I could change my code in my application, but I would have to query the database each time to check if the trade(record) already exists.

Any idea how I could get around this..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 13:18:34

what i was suggesting was to use the date value to distinguish records that were inserted last and then change all other execution status value to 2 for the condition

jrogers
Starting Member

34 Posts

Posted - 2008-10-08 : 13:30:22

so you are suggesting that once all my records have been added I run a stored procedure which does the maintanance? I think that would definitely be quicker and more effiecient than using a trigger (also as the data is not time critical, it doesn't matter if the data is incorrect for 20 seconds or so)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 13:55:43

quote:
Originally posted by jrogers

so you are suggesting that once all my records have been added I run a stored procedure which does the maintanance? I think that would definitely be quicker and more effiecient than using a trigger (also as the data is not time critical, it doesn't matter if the data is incorrect for 20 seconds or so)

no need of seperate procedure. you can incorporate this in insert procedure itself

jrogers
Starting Member

34 Posts

Posted - 2008-10-08 : 14:58:27

ok great thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources