February 18, 2016 at 1:14 am
Hello, what do you think ,,, any suggestions ?
I will have app which will need data from tracking table about 30/40rows ( this table is update an selected lot of times depends on moving some module on line )
every time I used procedure in timer per sec for getting data and depends on this I did some action..this select is very easy and with nolock so without problem.
I was thinking about use trigger, but I am not sure about this. But if I will use trigger, also will need to create some table and also use procedure for this another table.
What do you prefer and when ?
thank you
February 18, 2016 at 8:43 am
It isn't clear what you mean. This is best described with some code to show what you mean by move.
If you mean you have a timer in your application that calls a procedure to get data, and that works, what is the issue? If you are looking to replace this with a trigger, that likely won't work. Triggers execute based on a data modification action (insert/update/delete). This is when they fire and work, and they fire once only for the insert/update/delete statement, not per row.
February 18, 2016 at 10:37 am
Not enough description for us to really advise.
Triggers and sproc both have their place.
As does WITH (NOLOCK). However, to say that using that is not without problem is REALLY MISGUIDED/MISINFORMED!! This is especially true with concurrent access like you are doing where it certainly sounds like you could be open to concurrency issues.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 18, 2016 at 10:40 pm
Hello,
thank you for responses..
it was only thinking, because supplier for new app told me about using trigger, and I told him that we every time use procedure for getting some data from position
for example in some assembly shop , if some module (car) move to station we have to know that this module is in station or no , so we will get some data about this car like for example type of this or next specifications.. this is only for thinking, and I didnt see some advantages, so for improve my skill I write this for discussion..
so I want to know if there is some advantage, because he didnt know why he wants to use this..becuase like I was thinking there is needed create new table where trigger from tracking table will be inserted data after update and from new table will be also procedure in timer .
I know for what is trigger like Steve Jones - SSC Editor wrote... so this is reason why I didnt see advantages and I cannot ask to someone here about this.
February 19, 2016 at 2:35 am
To reiterate what Steve already said: your problem is not clear. Please post it in a language that we all understand on this forum: in the form of CREATE TABLE statements to show us how the table(s) look, INSERT statements with a small selection of sample data to illustrate the issue, any relevant code that you already have and the expected results.
February 21, 2016 at 5:18 pm
Triggers can move data, perform an insert/update/delete when there is an insert, update, delete operation on a table. The advantage of a trigger is it is always run when data in a table is altered. Whether you use a procedure or a query.
A procedure that performs a function cannot be guaranteed to always move data if there is a change in the table from a query.
It's unclear what you are thinking. The English you have used doesn't quite make sense. What would be helpful is if you be a little more specific and clear.
If I have a row updated in a module table, because the car moved, what do you need to happen? Insert a row in another table? Alter related data in another table?
Your language is too vague and unclear to understand what you want/need/or think you can accomplish.
February 21, 2016 at 9:17 pm
tony28 (2/18/2016)
Hello, what do you think ,,, any suggestions ?I will have app which will need data from tracking table about 30/40rows ( this table is update an selected lot of times depends on moving some module on line )
every time I used procedure in timer per sec for getting data and depends on this I did some action..this select is very easy and with nolock so without problem.
I was thinking about use trigger, but I am not sure about this. But if I will use trigger, also will need to create some table and also use procedure for this another table.
What do you prefer and when ?
thank you
What does the stored procedure do? Also, are you talking about auditing a table when it comes to using a trigger? What I mean my "auditing" is adding rows to an "audit" table whenever you make a change in a base table.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 22, 2016 at 2:58 pm
Hello,
sorry for my english and confusing... you have to understand that is not my native language..
Hugo Kornelis : I dont have problem with this. this is only thinking , why supplier wants use trigger, my opinion is that it will not select mainly table , but some temporary where trigger will insert data from main table..and procedure will be used every time...
Steve Jones - SSC Editor : like you wrote, it will be inserted to another table , but according to my opinion it is not needed. And procedure will used every time and also selected this table... I think that my question was wrong , because procedure will be used every time for get data from table to application.... like I wrote above based on my opinion, supplier only wants select temporary and not mainly table, or I dont know because he cannot explain to me, so I declined trigger and creating new table :]
Jeff Moden: like I wrote above, my question was wrong
guys thank you for patience, I hope that now is clear and understand what I was thinking before....
basicly new question is, they want use trigger for insert to new table from tracking table based on update .... procedure will be select from new table for get data to application.
My solution now is, that procedure selects directly from tracking table. If there is NOLOCK, I think that is not reason why do next operations with trigger right ? or I am thinking wrong again ?:]
February 22, 2016 at 3:17 pm
I'm still not quite sure what you mean with the language barrier, but maybe this helps.
A trigger is a stored procedure that runs when data in a table is modified. It runs once for a transaction on a table.
A stored procedure runs when you execute it.
They can both do the same tasks. However, if you have a procedure working on a timer, I'm not sure a trigger is any better.
In terms of NoLock, you can read incorrect data, which is why this is not recommended: http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/
February 22, 2016 at 3:29 pm
Steve Jones - SSC Editor (2/22/2016)
I'm still not quite sure what you mean with the language barrier, but maybe this helps.A trigger is a stored procedure that runs when data in a table is modified. It runs once for a transaction on a table.
A stored procedure runs when you execute it.
They can both do the same tasks. However, if you have a procedure working on a timer, I'm not sure a trigger is any better.
In terms of NoLock, you can read incorrect data, which is why this is not recommended: http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/
basicly I could write like this
example 1
application have to get data
procedure in application with timer with parameter will select 1row from Table1.
example 2
application have to get data,
trigger will insert into Table2 based on update in Table1
procedure in application with timer with parameter will select 1row from Table2.
definition:
Table1 has directly 40rows ( example is subway with 40stations, if train is moving to next station, update will happen)
procedure with parameter(unique station) get only 1row
1row is status like False or True ( is on station or no )
Trigger with insert to Table2 based on update False or True in Table1 ( for this nolock has no very big effect )
Table2 should have 1row
I think these last two is not needed and example 1 is more clear. or no?
February 22, 2016 at 3:32 pm
If you want to have code react to an update of a table, a trigger will handle that. A procedure can read from the audit table directly and return a set of data, but it'll be independent of the trigger. Like Steve said, it'll run when executed. The trigger will run when an update is performed and it will be completed before the full transaction is completed. The application can then call the procedure and read the data.
Like others have pointed out, be careful with NOLOCK. Unless you really understand it, my advice would be to avoid it.
February 22, 2016 at 6:17 pm
example 1
application have to get data
procedure in application with timer with parameter will select 1row from Table1
Why only select one row? Why table1 only? I still dont understand the full context that you are working under.
Do you have a table that get updated almost all the time and that makes the information displayed in the application old quickly? So you mentioned a subway moving and you want to see in real time where the train currently is? One minute it could be here and in the next somewhere else and you want the application to automatically refresh with this change quickly?
----------------------------------------------------
February 22, 2016 at 6:26 pm
MMartin1 (2/22/2016)
example 1
application have to get data
procedure in application with timer with parameter will select 1row from Table1
Why only select one row? Why table1 only? I still dont understand the full context that you are working under.
Do you have a table that get updated almost all the time and that makes the information displayed in the application old quickly? So you mentioned a subway moving and you want to see in real time where the train currently is? One minute it could be here and in the next somewhere else and you want the application to automatically refresh with this change quickly?
Table1 is main, already used and is updated from different current application.
Like you wrote, but I need check only one station in subway, because there is needed for example repair.. I hope that this example is more clear. So i have to know, when the train is there and repair team can go do their work..
So my question is this,
using procedure direct for Table1, which will be executed from new application for "repair'
or
use trigger for insert to another separate Table2 based on update on Table1 and procedure will be selected from Table2, which will be executed from new app...
what do you think ?
February 22, 2016 at 7:30 pm
I would point you to Jeff Moden post and see his link on how to post code problems. It would help us to better help you if you read through that and followed the guidance.
From what you briefly mention above though, and without knowing more, it seems simpler to use table1 instead of duplicating the same information to another table where it can be stale. But you know better. So take this advise with caution for I dont fully understand your situation.
----------------------------------------------------
February 22, 2016 at 7:34 pm
MMartin1 (2/22/2016)
I would point you to Jeff Moden post and see his link on how to post code problems. It would help us to better help you if you read through that and followed the guidance.From what you briefly mention above though, and without knowing more, it seems simpler to use table1 instead of duplicating the same information to another table where it can be stale. But you know better. So take this advise with caution for I dont fully understand your situation.
I cannot do this, because this is not problem. It was meant only for general discussion ,
I think for me is clear... like you wrote, why duplicate this table ... I also didnt understand of request from supplier for application. So I was also confused, and wrong explained first time...
thank you for patience
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply