Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 698
  • Last Modified:

Trigger

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
TransBind
Asked:
TransBind
  • 7
  • 4
  • 2
  • +1
3 Solutions
 
Einstine98Commented:
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
 
Einstine98Commented:
One more thing... if you are getting an erro on the first code or mine, please post it back
0
 
TransBindAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Einstine98Commented:
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
 
TransBindAuthor Commented:
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
 
Einstine98Commented:
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
 
Saqib KhanSenior DeveloperCommented:
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
 
Einstine98Commented:
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
 
Einstine98Commented:
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
 
Einstine98Commented:
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
 
TransBindAuthor Commented:
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
 
TransBindAuthor Commented:
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
 
Saqib KhanSenior DeveloperCommented:
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
 
Scott PletcherSenior DBACommented:
>> 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 7
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now