is it possible to invoke SQL trigger programmatically?

Question

I know under such circumstances, I have to use Stored Procedures,

but still I want to know if it is possible? If NO, Why? If YES, How?

Solution 1

By definition "a trigger" is a procedure that fires when a table is changed. I guess you could make it fire programmatically by doing update/delete/create on a table that has a trigger.

If you want a procedure that can be executed manually, then as you pointed out, you should just create a stored procedure.

If you want a procedure that can be executed as a trigger and manually, why not create a stored procedure and then create a trigger that simply has one line that fires that procedure?

If you are writing some test/diagnostics code and really need to invoke some trigger code, you might be able to use some meta API (I remember Oracle had something like that. Not sure about sql server, but it's got to have something) to extract the code out and massage it into a stored procedure. If you do this, as Alex_L already mentioned, you will have to somehow fake out the pseudo update rows which are typically accessible only to triggers.

Solution 2

First thing that comes to mind is

update yourtable
set yourcolumn = yourcolumn
-- consider a 'where' statement

I imagine this would invoke the trigger without changing anything. Thus being invoked with code.

Solution 3

You cannot call triggers directly. They are fired automatically when you perform an insert/update or delete on a table that has triggers. Therefore, you cannot call a trigger in a stored procedure. Trigger needs to have deleted or inserted record when executes, and I cannot see how it can be passed...