Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Trigger

Posted on 2006-06-28
14
Medium Priority
?
695 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
ID: 17006298
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
ID: 17006299
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
ID: 17006319
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 12

Expert Comment

by:Einstine98
ID: 17006338
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
ID: 17006397
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 800 total points
ID: 17006611
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:Saqib Khan
ID: 17006861
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
 
LVL 12

Expert Comment

by:Einstine98
ID: 17007416
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
ID: 17007423
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
ID: 17007438
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
ID: 17009314
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
ID: 17009432
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:Saqib Khan
Saqib Khan earned 800 total points
ID: 17010125
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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 17010394
>> 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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

876 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