Cuando queremos ejecutar una conjunto de sentencias en SQL Server automáticamente antes o después de una operación en una tabla, hacemos uso de los triggers (disparadores).

Para empezar usaremos la base de datos Northwind y agregaremos un trigger justo después de la operación de insertar o actualizar los registros de la tabla detalle de pedido.

Según la documentación oficial de Microsoft SQL Server la sintáxis de los triggers se trabajan de la siguiente manera:

-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)  
  
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table | view }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER | INSTEAD OF }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
[ WITH APPEND ]  
[ NOT FOR REPLICATION ]   
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }  
  
<dml_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]  
  
<method_specifier> ::=  
    assembly_name.class_name.method_name

Fuente: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017

Agregamos el siguiente script para crear nuestro trigger:

Este trigger permite actualizar el stock despues de realizar la inserción o actualización de datos sobre la tabla Detalles de Pedidos. Hacemos uso de la tabla especial inserted para obtener los registros que se ingresaron en la tabla Detalles de Pedidos y cada uno de ellos los almacenamos en variables como @CAN -> obtener la cantidad, @PRO -> obtener el id del producto, @STOCK -> obtener el stock del producto ingresado.

Luego realizamos una condición en caso de que se exceda el stock se obtiene mediante la sentencia RAISERROR el mensaje de error respectivo. Si la condicion es que se cumpla que la cantidad sea menor o igual al stock entonces se realizará la actualización del stock a la tabla productos según la cantidad registrada en el Detalle de Pedidos restando de esta forma el stock de los productos.