Use when you need to generate, validate, or deploy stored procedures for PostgreSQL, MySQL, or SQL Server.
Creates database functions, triggers, and procedures with proper error handling and transaction management.
Trigger with phrases like "generate stored procedure", "create database function", "write SQL procedure",
"add trigger to table", or "create CRUD procedures".
Generate production-ready stored procedures for PostgreSQL, MySQL, and SQL Server with proper error handling, transaction management, and security best practices.
Target database type identified (PostgreSQL, MySQL, or SQL Server)
Instructions
Step 1: Identify Database Type and Requirements
Determine the target database and procedure requirements:
-- PostgreSQL: Check version and extensions
SELECT version();
\dx
-- MySQL: Check version and settings
SELECT VERSION();
SHOW VARIABLES LIKE 'sql_mode';
-- SQL Server: Check version and edition
SELECT @@VERSION;
Step 2: Generate Stored Procedure
PostgreSQL Function (PL/pgSQL):
CREATE OR REPLACE FUNCTION get_user_by_id(p_user_id INTEGER)
RETURNS TABLE(id INTEGER, username VARCHAR, email VARCHAR, created_at TIMESTAMP)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
SELECT u.id, u.username, u.email, u.created_at
FROM users u
WHERE u.id = p_user_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'User with ID % not found', p_user_id
USING ERRCODE = 'P0002';
END IF;
END;
$$;
MySQL Stored Procedure:
DELIMITER //
CREATE PROCEDURE GetUserById(IN p_user_id INT)
BEGIN
DECLARE user_exists INT DEFAULT 0;
SELECT COUNT(*) INTO user_exists FROM users WHERE id = p_user_id;
IF user_exists = 0 THEN
SIGNAL SQLSTATE '45000' # 45000 = configured value
SET MESSAGE_TEXT = 'User not found';
END IF;
SELECT id, username, email, created_at
FROM users
WHERE id = p_user_id;
END //
DELIMITER ;
SQL Server Stored Procedure (T-SQL):
CREATE PROCEDURE dbo.GetUserById
@UserId INT
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS (SELECT 1 FROM dbo.Users WHERE Id = @UserId)
BEGIN
RAISERROR('User with ID %d not found', 16, 1, @UserId);
RETURN;
END
SELECT Id, Username, Email, CreatedAt
FROM dbo.Users
WHERE Id = @UserId;
END;
GO
Step 3: Add Transaction Management
PostgreSQL with Transaction:
CREATE OR REPLACE FUNCTION transfer_funds(
p_from_account INTEGER,
p_to_account INTEGER,
p_amount NUMERIC(15,2)
)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
-- Debit source account
UPDATE accounts SET balance = balance - p_amount
WHERE id = p_from_account AND balance >= p_amount;
IF NOT FOUND THEN
RAISE EXCEPTION 'Insufficient funds or invalid source account';
END IF;
-- Credit destination account
UPDATE accounts SET balance = balance + p_amount
WHERE id = p_to_account;
IF NOT FOUND THEN
RAISE EXCEPTION 'Invalid destination account';
END IF;
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
$$;
MySQL with Transaction:
DELIMITER //
CREATE PROCEDURE TransferFunds(
IN p_from_account INT,
IN p_to_account INT,
IN p_amount DECIMAL(15,2)
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
START TRANSACTION;
UPDATE accounts SET balance = balance - p_amount
WHERE id = p_from_account AND balance >= p_amount;
IF ROW_COUNT() = 0 THEN
SIGNAL SQLSTATE '45000' # 45000 = configured value
SET MESSAGE_TEXT = 'Insufficient funds';
END IF;
UPDATE accounts SET balance = balance + p_amount
WHERE id = p_to_account;
COMMIT;
END //
DELIMITER ;
Step 4: Validate Syntax
Use the validation script to check procedure syntax:
# Deploy to PostgreSQL
python3 ${CLAUDE_SKILL_DIR}/scripts/stored_procedure_deployer.py \
--db-type postgresql \
--host localhost \
--database mydb \
--file procedure.sql
# Deploy to MySQL
python3 ${CLAUDE_SKILL_DIR}/scripts/stored_procedure_deployer.py \
--db-type mysql \
--host localhost \
--database mydb \
--file procedure.sql
Output
SQL procedure file with proper syntax for target database
Validation report confirming syntax correctness
Deployment confirmation with execution results
Rollback script for procedure removal
Error Handling
Error
Cause
Solution
permission denied
Missing CREATE PROCEDURE privilege
GRANT CREATE PROCEDURE ON database TO user;
syntax error
Invalid SQL for database type
Use database-specific syntax validator
function already exists
Procedure exists without OR REPLACE
Add OR REPLACE or DROP first
undefined column
Referenced column doesn't exist
Verify table schema before deployment
transaction aborted
Error during transaction
Check EXCEPTION handler and ROLLBACK logic
Examples
Generate CRUD procedures for a table:
User: Generate CRUD stored procedures for the 'products' table in PostgreSQL
Claude: I'll create four procedures for the products table:
1. create_product - Insert new product
2. get_product - Retrieve by ID
3. update_product - Update existing product
4. delete_product - Soft delete product
Create audit trigger:
User: Create a trigger to log all changes to the orders table
Claude: I'll create an audit trigger that:
1. Creates an orders_audit table if not exists
2. Captures INSERT, UPDATE, DELETE operations
3. Records old/new values, user, and timestamp
