SQL Insert Trigger
-
Hi all.
If I have a table such as :
Queue
- queue_id Int (PK)
- Action Char(15)
- Info varchar(max)
If I want to create a trigger that calls a stored proc upon insert, can I? What would happen if there were 20 records added at the same time? Would I get a deadlock issue?
Thanks,
Mike
-
Since a trigger is just a special kind of SP, I believe you could.
Better though if you kept the code in the trigger rather than reaching out to another one, unless you're doing it because the SPs code is used elsewhere.
You want to minimize the amount of work done by the trigger, so you should be asking yourself if this is really necessary.
If the INSERT is for multiple records, there's an INSERTED pseudo-table available in the trigger to query against to find out what was inserted (assuming this is an AFTER INSERT trigger). INSERTED is also availabe in INSTEAD OF triggers but in that case the records haven't been inserted into the table yet.
Deadlocking would depend more on what the called SP was doing than the number of records inserted.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
The plan is for a website to put in requests in the Queue table and SQL to handle the requests. The called SP would be something like this:
parameter @id int
DECLARE @action char(30)
select @action = action from queue where queue_id = @id
IF @action = 'ADD ORDER' exec dbo.q_addorder @id
IF @action = 'UPDATE ADDRESS' exec dbo.q_updateaddress @id
...
I have a website that wants to update data. I want to control access to the data and not let the website add data directly to the tables. Each SP will have checks, etc. to handle data sent into the queue.
Mike
-
mike 57299 (1/24/2013)
The plan is for a website to put in requests in the Queue table and SQL to handle the requests. The called SP would be something like this:parameter @id int
DECLARE @action char(30)
select @action = action from queue where queue_id = @id
IF @action = 'ADD ORDER' exec dbo.q_addorder @id
IF @action = 'UPDATE ADDRESS' exec dbo.q_updateaddress @id
...
I have a website that wants to update data. I want to control access to the data and not let the website add data directly to the tables. Each SP will have checks, etc. to handle data sent into the queue.
Mike
Personally, I don't think this is such a great idea.
Why not instead have a background process that runs every 5 or 10 minutes to process any requests in the queue that have not been processed? It would mean that requests are not processed immediately but depending on the work going on in those called SPs with respect to validation, might prove more effective because the background process could tag each request as processed or invalid (with an error code indicating why).
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
mike 57299 (1/24/2013)
The plan is for a website to put in requests in the Queue table and SQL to handle the requests. The called SP would be something like this:parameter @id int
DECLARE @action char(30)
select @action = action from queue where queue_id = @id
IF @action = 'ADD ORDER' exec dbo.q_addorder @id
IF @action = 'UPDATE ADDRESS' exec dbo.q_updateaddress @id
...
I have a website that wants to update data. I want to control access to the data and not let the website add data directly to the tables. Each SP will have checks, etc. to handle data sent into the queue.
Mike
You can configure your websites login to deny access to tables directly and just assign permission to execute the necessary procs. Then the website can just call q_addorder, q_updateaddress, etc.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply