Solved

Trigger

Posted on 2006-06-28
14
686 Views
Last Modified: 2008-02-07
Is there a logical mistake in the below trigger. What I am asking for is an advice ... is it possible for this trigger to execute concurrently and pass @TimeCardID of another user to a procedure dbo.SynchINSCost. If it is possible, how can it be prevented?

CREATE trigger dbo.trInsertTimeCard
  on dbo.tblTimeCard
  for insert
as
 
  set nocount on
  declare @TimeCardID int

  select @TimeCardID = tc.int_TimeCardID from tblTimeCard tc
  inner join inserted i on
  i.int_TimeCardID = tc.int_TimeCardID

  exec dbo.SynchINSCost @TimeCardID

GO
0
Comment
Question by:TransBind
  • 7
  • 4
  • 2
  • +1
14 Comments
 
LVL 12

Expert Comment

by:Einstine98
Comment Utility
You should not try to user INSERT triggers assuming you will have a single value in the "inserted" table...

so assuming that you have to pass a single value to dbo.SynchINSCost, you have to do a cursor


CREATE trigger dbo.trInsertTimeCard
  on dbo.tblTimeCard
  for insert
as
 
  set nocount on
  declare @TimeCardID int
DECLARE TimeCursor Cursofr FOR
  select  int_TimeCardID
  from INSERTED

OPEN TimeCursor

WHILE (1=1)
BEGIN
FETCH NEXT FROM TimeCursor into @TimeCardID
IF @@Fetch_Status = 0 BREAK

  exec dbo.SynchINSCost @TimeCardID
END
Close TimeCursor
Deallocate TimeCursor


Having said that,  I would rather include the code of syncinscost into the trigger and do the actual job there without a cursor if possible.

0
 
LVL 12

Expert Comment

by:Einstine98
Comment Utility
One more thing... if you are getting an erro on the first code or mine, please post it back
0
 
LVL 7

Author Comment

by:TransBind
Comment Utility
I am a little bit confused now ...
I will not have a single value in the tblTimeCard. Why do I have to use a cursor? What can I accomplish? The code for trInsertTimeCard works. However, I am concerned about a possibility of two users inserting data simultaneously and a wrong @TimeCardID passed to dbo.SynchINSCost procedure. Is that even possible?


0
 
LVL 12

Expert Comment

by:Einstine98
Comment Utility
If an insert or an update is done for more than one record (through the application or directly through query analyzer) your code will fail ...

Say if I issue the command in query analyzer

UPDATE tblTimeCard SET X = Y where z = c

and that happens to update more than one record, your trigger will cause the transaction to fail.

If (assuming only one update happens at a time) using your code two users update the same record the last update will win basically...

If it is inserts... no conflict will be there, except if the two inserts use the same ID which should be blocked by your PrimaryKey flag on the table ID field....

so... there should be no problem, but the code is designed for single row updates and inserts... anything else will fail.

Hope this helps...
0
 
LVL 7

Author Comment

by:TransBind
Comment Utility
yes it helps and makes sense ...

for an update trigger would i have to replace this code

 select @TimeCardID = tc.int_TimeCardID from tblTimeCard tc
  inner join inserted i on
  i.int_TimeCardID = tc.int_TimeCardID

with this code:

 select @TimeCardID = tc.int_TimeCardID from tblTimeCard tc
  inner join updated i on
  i.int_TimeCardID = tc.int_TimeCardID

notice i replaced 'inserted' with 'updated' keyword






0
 
LVL 12

Accepted Solution

by:
Einstine98 earned 200 total points
Comment Utility
Updates and inserts are sent to INSERTED table
Deletes are sent to DELETED table

You need to get your trigger to fire for both inserts and updates using the same logic if the business requirement is the same...

If you need to handle updates differently, I would suggest using the same trigger and checking if the transaction was update or insert below is the books online help about this

QUOTE
A. Use the IF UPDATE clause to test data modifications
This example creates an INSERT trigger my_trig on table my_table and tests whether column b was affected by any INSERT statements.

CREATE TABLE my_table*
(a int NULL, b int NULL)
GO

CREATE TRIGGER my_trig
ON my_table
FOR INSERT
AS
IF UPDATE(b)
   PRINT 'Column b Modified'
GO

B. Use the COLUMNS UPDATED() clause to test data modifications
This example obtains similar results using the COLUMNS_UPDATED() clause.

CREATE TRIGGER my_trig2
ON my_table
FOR INSERT
AS
IF ( COLUMNS_UPDATED() & 2 = 2 )
   PRINT 'Column b Modified'
GO

0
 
LVL 23

Expert Comment

by:adilkhan
Comment Utility
Einstine98 are you sure about your statement?

in SQL even if you doa BULK insert it still goes by "Row by Row" level. if you doing a bulk insert using QA or Application your Trigger will still work fine without any problem.

there is nothing wrong with your Code Tranbind, its fine.
and also please stay away from Cursors, a Cursor will return each row as a new rowset, there are many ways to use something else instead of cursors.

here is a example with BULK INSET and works fine.


create table test
(sku varchar(1000) null)

create table test2
(sku varchar(1000) null)


Create Trigger TRG
on test for insert
as


      insert into test2
      Select sku from inserted

go





insert into test
Select top 100 stk_code  from someTable

Select * from test
Select * from test2
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 12

Expert Comment

by:Einstine98
Comment Utility
Try this :

Insert into table_x
SELECT * from TableY

I am an ETL specialist :-) guaranteed... I face this kind of triggers and deal with it every day....
0
 
LVL 12

Expert Comment

by:Einstine98
Comment Utility
sorry, i didn't read the whole of your examples... but I am positive that this kind of update/insert will cause such trigger to fail... you can also research the problem... we just faced few months back on major website on the internet... so... no doubt in my mind....

INSERTED should be used as a table join and NOT single row entry...
0
 
LVL 12

Expert Comment

by:Einstine98
Comment Utility
One more thing...


Your trigger is different to the trigger presented in the example... he is assigning the value from the select to a variable...

SELECT @variable = Column
FROM INSERTED

that's the problem that will make it fail.... if the result set is a single record.. no problem.. if you have more than one record (which you will in your example)... then it's going to fail....
0
 
LVL 7

Author Comment

by:TransBind
Comment Utility
Is ETL specialist cooler than MCSD? :) Since we have conclued that inserts are done row by row in sql even in the case of bulk insert. Therefore, if I am assigning the value from the select to a variable I believe it will pass that value each time to a procedure. How would I modify my trigger to work in the same fashion for an update. Is this correct?

 select @TimeCardID = tc.int_TimeCardID from tblTimeCard tc
  inner join updated i on
  i.int_TimeCardID = tc.int_TimeCardID

exec dbo.SynchUPDCost @TimeCardID


0
 
LVL 7

Author Comment

by:TransBind
Comment Utility
Ignore my last comment about how would i modify my trigger to work for an update.
From the below two examples which one will be fired when update querry is issued?

CREATE TRIGGER my_trig
ON my_table
FOR INSERT
AS
IF UPDATE(b)
   PRINT 'Column b Modified'
GO

B. Use the COLUMNS UPDATED() clause to test data modifications
This example obtains similar results using the COLUMNS_UPDATED() clause.

CREATE TRIGGER my_trig2
ON my_table
FOR INSERT
AS
IF ( COLUMNS_UPDATED() & 2 = 2 )
   PRINT 'Column b Modified'
GO
0
 
LVL 23

Assisted Solution

by:adilkhan
adilkhan earned 200 total points
Comment Utility
CREATE TRIGGER my_trig
ON my_table
FOR UPDATE
AS
IF UPDATE(b)
   PRINT 'Column b Modified'
   -- You still use INSERTED to refer to the table
GO
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 100 total points
Comment Utility
>> Since we have conclued that inserts are done row by row in sql even in the case of bulk insert. <<

You're reached a false conclusion.  SQL Server does *NOT* call a trigger for every row of a modification; it calls it *ONCE* for the entire *STATEMENT*.  You can verify that very easily:

CREATE TABLE test1 (col1 INT)
GO
CREATE TRIGGER test1_trig1
ON test1
AFTER INSERT
AS
PRINT  'Trigger Fired'
GO
INSERT INTO test1
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
SELECT COUNT(*)
FROM test1

Notice that you get *ONE* "Trigger Fired" message but *THREE* rows inserted.


>> Therefore, if I am assigning the value from the select to a variable I believe it will pass that value each time to a procedure. <<

As just shown, that is not true.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now