I just want to know if/how a fellow would perform the same function that 'ON INSERT' does in a trigger...without a trigger!
Rich
What do you want to do in this "non-trigger"? You should be able to use Extended Events. Haven't tried but they cover most everything
basically, detect when a table has had an INSERT or UPDATE run against it..
well, and act on it as well, like fire off a S/Proc
You need a trigger. Tracing and events are fine for analysis but can't replace a trigger
1 Like
ok.
Thanks.
R
Why is using a trigger a problem? Any issues with triggers are almost always from poorly written triggers. A well-written trigger shouldn't cause you any issues.
ok, I'll fess up:
- the trigger I wrote, called a storedProc after INSERT/UPDATE, and said S/Proc had a line of code where it WROTE to the table I just UPDATED...a nice little(endless) loop.
- I have a friend, who is extremely knowledgeable of things SQL'ish, and he recoiled at a trigger at all, and pointed to this error on my part as exhibit#1 why he stays away from them..
But I'm not even finding a viable(easy to implement) alternative to ON INSERT,UPDATE
Rich
How much latency are you willing to tolerate?
You can enable Change Tracking and write a job that processes new rows every minute or so.
welp, essentially, I'm taking in a row of data into the table in question, at a(sporadic) rate of like 5 /minute
not exactly..busy
I'm then acting on the table UPDATE or INSERT, in order to then update ANOTHER , larger VERY busy Database/table.
So, I'm needing to act on the INSERT,UPDATE , and facilitate some means of skipping rows already processed: in fact, at the moment, I'm trying to update the first table with a Bit called 'Processed' that I can act on... not sure if that's UnSmart or not..
thx,
R
There are two settings in SQL Server - nested triggers and recursive triggers - that lets you control whether a trigger can be invoked recursively. One is a server level option and the other is a database level option.
https://msdn.microsoft.com/en-us/library/ms190739.aspx
If you do change those settings, be sure to do sufficient testing to ensure that other triggers (perhaps in other databases on the same server) that may rely on the default behavior (perhaps inadvertently) are not affected.
thanks
I'm currently looking into & trying out the CHANGE TRACKING options..
Yes, you could use CDC but it's not inline code. If you;re OK to batch update your big table periodically, this might work
Your friend may well be very knowledgeable on SQL overall, but there is absolutely nothing wrong with using triggers; indeed, they are often a critical component of the logic.
If you'll post the trigger you have, I can re-work the code for you.
CDC and triggers do two different things. If a trigger will do what you need, you don't need CDC, although you could perhaps force CDC to allow you to do the same thing a trigger would have.
So, I'm thinking of using a job, to fire off a S/P every 5 minutes, look for rows with a Bit set to 0
and run the code.
I'm stumbling & bumbling over the 'looping' through the rows, and performing the needed update against the current ROW..
What I've come up with so far is:
Begin Transaction
Declare rowLooper Cursor FOR
--Update P21Play.dbo.oe_hdr
--Set ups_code = (
(Select tracking_no
FROM distinctOrderNo b
INNER JOIN P21Play.dbo.oe_hdr a
ON b.order_no = a.order_no
WHERE b.order_no = a.order_no
AND b.Processed = 0)
--Update P21Play.dbo.oe_hdr
--Set ups_code = ???
Open rowLooper
Fetch Next from rowLooper
Close rowLooper
DEALLOCATE rowLooper
ROLLBACK TRANSACTION
You can see where I tried doing an update against the select statement, received 'Incorrect syntax near Update) then thought what if I were able to assign a variable to ...something that the cursor operation spits out for a ROW value...
not happening so far.
If I run just this highlighted:
Select tracking_no
FROM distinctOrderNo b
INNER JOIN P21Play.dbo.oe_hdr a
ON b.order_no = a.order_no
WHERE b.order_no = a.order_no
AND b.Processed = 0
it returns 7 rows, so I can see the logic is working, as I verified that the order_no values are indeed matchign between databases..
I duno, maybe this all is more than you wanna putz with...
Rich
ps this is what some table results look like(distinctOrderNo)
I was thinking of tagging on a set Processed = 1 after I do the Update...so that the S/P knows which ones to NOT look at..?
You don't need a cursor, just
update a
set a.ups_code = b.tracking_no
from P21Play.dbo.oe_hdr a
join distinctOrderN b
ON b.order_no = a.order_no
WHERE b.Processed = 0huh..
why did I use such a convoluted approach..
no wait, don;t answer that
ok, many thanks: I'll see about scheduling periodic select/updates via SQL job!
R
In theory that will work, of course. But, just to avoid a trigger, you have a delay in sync'ing the data, another process that could fail and thus cause data not to be sync'd, and you still have to tune the UPDATE to make sure it can run very quickly since it runs so often.
yeah, a delay is quite acceptable, in this case..
I could really, even stretch updates out several minutes and this would be a major improvement over current workflow..
Thanks!
Rich