Can i execute a stored procedure from a trigger

It is perfectly possible to call stored procedures from trigger. This is not the best for efficiency, so you may need to rewrite your procedures to work with set-based input. Whether a trigger would fit in your case, I don't know. An alternative is to call a stored procedure on the various Onxxxx events that performs the job. A trigger should been seen as part of the application, but rather part of the database itself from what I said above.

There are many MANY examples of trigger code in the forums. Executing a stored procedure via tsql is no different just because that statement is in a trigger. But I'll warn you against this approach for many reasons. First and foremost is that efficient and effective trigger code is an advanced topic and is likely beyond your skillset.

Secondly, most every developer writing code for a trigger assumes, incorrectly, that a single row is affected when the trigger executes and embeds this assumption in the logic. BTW - you skipped the delete logic. Are you certain that you do not need to consider this action? And beware the simplistic assumption in your description.

A merge statement can insert new rows, update existing rows, and delete existing rows in one pass. So you intend to write a single stored procedure and two or three? Your stored procedure will need to handle multiple situations and multiple rows - making the logic far more complex than you imagine.

Triggers can be disabled by people with sufficient permissions. You could certainly consider to use Service Broker for having asyn activity, e. Worse of that, every time you move or upload a database into another environment triggers are easily forgotten or dismissed. Krushna Kadam Posted March 17, 0 Comments. Joe Begenwald Posted March 17, 0 Comments. Anonymous Posted March 31, 0 Comments. Register or Login. Welcome back! Reset Your Password We'll send an email with a link to reset your password.

Stay ahead! Get the latest news, expert insights and market research, tailored to your interests. Sign in with email Enter the email address associated with your account. You auth link is expired or incorrect, please try again. Session system variables affect the individual user session. System variables that are dynamic can also be changed in MySQL stored procedures.

If global. The following example shows some of these syntax options. A constant must be initialized in its declaration, and no further assignments to the constant are allowed. User variables in MySQL stored procedures can be emulated in Oracle by defining the variables in a package. The package specification emulates the per-session MySQL user variables. Variables defined in a package are available to the users of the package. Each SQL statement in the stored procedure is checked for errors before the next statement is processed.

If an error occurs, control immediately is passed to an error handler. For example, if a SELECT statement does not find any rows in the database, an error is raised, and the code to deal with this error is executed. In MySQL stored procedures, handlers can be defined to deal with errors or warnings that occurs from executing a SQL statement within a stored procedure. The two types of handlers differ from their next point of execution in the stored procedure after the handler is run.

Handlers are defined to deal with one or more conditions. A condition may be defined separately with a name and subsequently referenced in the handler statement. All the handler definitions are made at the start of a compound statement block.

Exceptions may be internally defined by the runtime system or user-defined. Internal exceptions are implicitly automatically raised by the runtime system. User-defined exceptions must be given names and must be raised explicitly by RAISE statements in the stored procedures.