安全性与完整性

-- sql
-- 将员工表的姓名和部门列的 INSERT 和 UPDATE 权限授予招聘专员角色
-- 该角色只能操作指定的列，无法触及工资、社会保障号等敏感字段
GRANT INSERT(emp_name, department_id), UPDATE(emp_name, department_id)
ON TABLE employees
TO 'recruiter_role'@'localhost';

-- sql
-- 将表 employees 上的 SELECT 权限授予用户 alice
-- WITH GRANT OPTION 允许 alice 将该权限转授给他人
GRANT SELECT
ON TABLE employees
TO 'alice'@'hostname'
IDENTIFIED BY 'alice_password'
WITH GRANT OPTION;

-- sql
-- 步骤一：创建各种业务角色
CREATE ROLE 'teller_role';          -- 柜员角色
CREATE ROLE 'manager_role';          -- 经理角色
CREATE ROLE 'auditor_role';          -- 审计员角色
 
-- 步骤二：为柜员角色授予基础权限
-- 柜员可以查询客户信息、办理存取款业务、查询交易流水
GRANT SELECT, INSERT ON TABLE customers TO 'teller_role';
GRANT SELECT, INSERT, UPDATE ON TABLE accounts TO 'teller_role';
GRANT SELECT ON TABLE transactions TO 'teller_role';
 
-- 步骤三：为经理角色授予更高级的权限
-- 经理在柜员权限基础上，增加了账户销户、交易审核等权限
GRANT DELETE ON TABLE accounts TO 'manager_role';
GRANT ALL PRIVILEGES ON TABLE employees TO 'manager_role';
-- 注意：ALL PRIVILEGES 并不等同于授予所有可能的权限，它只包含
-- INSERT, SELECT, UPDATE, DELETE, REFERENCES, INDEX, ALTER, CREATE, DROP
-- 不包括 GRANT OPTION 和 DDL 之外的特殊权限
 
-- 步骤四：将角色分配给具体用户
GRANT 'teller_role' TO 'zhang_san'@'localhost';
GRANT 'manager_role' TO 'li_si'@'localhost';
-- 一个用户可以同时属于多个角色
GRANT 'teller_role', 'manager_role' TO 'wang_wu'@'localhost';
 
-- 步骤五：设置默认激活的角色
-- 用户连接数据库时，自动启用 teller_role
SET DEFAULT ROLE 'teller_role' FOR 'zhang_san'@'localhost';

-- sql
-- 撤销用户 alice 对 employees 表的 SELECT 权限
-- RESTRICT 关键字表示如果该权限已被转授出去，则拒绝撤销操作
-- 管理员需要先撤销所有下游的转授，然后再执行此撤销操作
REVOKE SELECT
ON TABLE employees
FROM 'alice'@'hostname'
RESTRICT;
 
-- 撤销权限并同时撤销 GRANT OPTION
-- 如果 alice 曾将权限转授给其他人，这些下游授权也会被撤销
REVOKE GRANT OPTION ON SELECT
ON TABLE employees
FROM 'alice'@'hostname';

-- sql
-- 查看当前 MySQL 用户及其全局权限
-- mysql.user 表存储了每个用户的认证信息和全局权限
SELECT user, host, select_priv, insert_priv, update_priv, delete_priv
FROM mysql.user
WHERE user = 'alice';
 
-- 查看某个用户对特定数据库的权限
-- mysql.db 表记录了数据库级别的权限分配
SELECT db, user, host, select_priv, insert_priv, update_priv, delete_priv
FROM mysql.db
WHERE user = 'alice' AND db = 'hr_system';
 
-- 查看某个用户对特定表的权限
-- mysql.tables_priv 表记录了表级和列级的详细权限信息
SELECT * FROM mysql.tables_priv
WHERE user = 'alice';
 
-- 查看某个用户对特定列的权限
-- mysql.columns_priv 表记录了列级权限的详细信息
SELECT * FROM mysql.columns_priv
WHERE user = 'alice';
 
-- 使用 SHOW GRANTS 命令快速查看用户的完整权限清单
-- 这是 DBA 日常巡检中最常用的命令
SHOW GRANTS FOR 'alice'@'hostname';
 
-- 查看所有用户的权限总览（需要管理员权限）
SHOW GRANTS FOR PUBLIC;  -- 查看授予 PUBLIC（所有用户）的权限

// kotlin
// 安全实践：在应用私有目录中创建数据库
class SecureDatabaseHelper(context: Context) : SQLiteOpenHelper(
    context,
    "secure_app.db",           // 数据库文件名，位于应用私有目录
    null,                       // 使用默认的 CursorFactory
    1,                          // 数据库版本号
    SQLiteDatabase.OPEN_READWRITE or SQLiteDatabase.CREATE_IF_NECESSARY
) {
    // 数据库文件路径示例：/data/data/com.example.app/databases/secure_app.db
    // 该路径受到 Android Linux 内核级别的 UID 隔离保护
 
    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL("""
            CREATE TABLE IF NOT EXISTS user_credentials (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                -- 绝对不要在设备端存储明文密码！
                -- 正确做法：存储密码的加盐哈希值
                password_hash TEXT NOT NULL,
                salt TEXT NOT NULL,
                created_at INTEGER NOT NULL
            )
        """.trimIndent())
    }
}

// kotlin
// 使用 SQLCipher 对数据库进行加密
// 需要在 build.gradle 中添加依赖：net.zetetic:android-database-sqlcipher:4.5.4
class EncryptedDatabaseHelper(
    private val context: Context,
    private val passphrase: ByteArray  // 密钥，应当来源于用户输入或安全存储
) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
 
    private val database: SQLiteDatabase
        get() {
            // 加载 SQLCipher 原生库
            System.loadLibrary("sqlcipher")
            // 通过 SupportFactory 创建支持加密的数据库实例
            val factory = SupportFactory(passphrase)
            return database
        }
 
    // 加密密钥的管理是整个方案中最关键也最困难的部分
    // 绝对不要将密钥硬编码在源代码中——这相当于把钥匙放在锁旁边
    // 推荐使用 Android Keystore 系统来安全地存储和获取密钥
}

// kotlin
// 使用 Android Keystore 安全地管理数据库加密密钥
object KeystoreManager {
    private const val KEY_ALIAS = "database_encryption_key"
    private const val ANDROID_KEYSTORE = "AndroidKeyStore"
    private const val PREFS_NAME = "secure_prefs"
    private const val ENCRYPTED_KEY_PREF = "encrypted_db_key"
 
    fun getOrCreateDatabaseKey(context: Context): ByteArray {
        val sharedPrefs = context.getSharedPreferences(PREFS_NAME, Context.MODE_PRIVATE)
 
        // 方案流程：
        // 1. 尝试从 SharedPreferences 中加载已加密存储的数据库密钥
        // 2. 如果不存在，则生成一个新的随机数据库密钥
        // 3. 用 Keystore 中的主密钥对数据库密钥进行加密后存储
        // 4. 返回解密后的数据库密钥供 SQLCipher 使用
 
        val encryptedKeyBase64 = sharedPrefs.getString(ENCRYPTED_KEY_PREF, null)
 
        return if (encryptedKeyBase64 != null) {
            // 已存在密钥，需要解密后使用
            val encryptedKey = android.util.Base64.decode(encryptedKeyBase64, android.util.Base64.DEFAULT)
            decryptKey(encryptedKey)  // 使用 Keystore 密钥解密
        } else {
            // 首次运行，需要生成并存储新密钥
            val newKey = generateRandomDatabaseKey()
            val encryptedKey = encryptKey(newKey)  // 用 Keystore 加密
            sharedPrefs.edit()
                .putString(ENCRYPTED_KEY_PREF, android.util.Base64.encodeToString(encryptedKey, android.util.Base64.DEFAULT))
                .apply()
            newKey
        }
    }
 
    private fun generateRandomDatabaseKey(): ByteArray {
        // 生成 256 位的随机数据库密钥
        val keyGenerator = KeyGenerator.getInstance("AES")
        keyGenerator.init(256, SecureRandom())
        return keyGenerator.generateKey().encoded
    }
 
    private fun encryptKey(key: ByteArray): ByteArray {
        // 获取或创建 Keystore 中的主密钥（用于加密数据库密钥）
        val keyStore = KeyStore.getInstance(ANDROID_KEYSTORE).apply { load(null) }
 
        val masterKey: SecretKey = if (keyStore.containsAlias(KEY_ALIAS)) {
            // 已存在，直接加载
            (keyStore.getEntry(KEY_ALIAS, null) as KeyStore.SecretKeyEntry).secretKey
        } else {
            // 创建新的主密钥，设置在设备重启后仍可使用（user-authenticated 暂不要求）
            val keyGenerator = KeyGenerator.getInstance(
                KeyProperties.KEY_ALGORITHM_AES,
                ANDROID_KEYSTORE
            )
            val spec = KeyGenParameterSpec.Builder(
                KEY_ALIAS,
                KeyProperties.PURPOSE_ENCRYPT or KeyProperties.PURPOSE_DECRYPT
            )
                .setBlockModes(KeyProperties.BLOCK_MODE_GCM)
                .setEncryptionPaddings(KeyProperties.ENCRYPTION_PADDING_NONE)
                .setKeySize(256)
                .setUserAuthenticationRequired(false)  // 若设为 true，需配合生物认证
                .build()
 
            keyGenerator.init(spec)
            keyGenerator.generateKey()
        }
 
        // 使用主密钥加密数据库密钥
        val cipher = Cipher.getInstance("AES/GCM/NoPadding")
        cipher.init(Cipher.ENCRYPT_MODE, masterKey)
        val encryptedBytes = cipher.doFinal(key)
        // 将 IV 和密文合并存储（GCM 模式需要 IV 进行解密）
        return cipher.iv + encryptedBytes
    }
 
    private fun decryptKey(encryptedKey: ByteArray): ByteArray {
        // 从存储的加密密钥中提取 IV 和密文
        val iv = encryptedKey.copyOfRange(0, 12)  // GCM 模式的 IV 固定 12 字节
        val cipherText = encryptedKey.copyOfRange(12, encryptedKey.size)
 
        val keyStore = KeyStore.getInstance(ANDROID_KEYSTORE).apply { load(null) }
        val masterKey = (keyStore.getEntry(KEY_ALIAS, null) as KeyStore.SecretKeyEntry).secretKey
 
        val cipher = Cipher.getInstance("AES/GCM/NoPadding")
        cipher.init(Cipher.DECRYPT_MODE, masterKey, GCMParameterSpec(128, iv))
        return cipher.doFinal(cipherText)
    }
}

// kotlin
// Repository 模式中的安全数据访问
class SecureUserRepository(
    private val userDao: UserDao,
    private val encryptedPreferences: EncryptedSharedPreferences
) {
    // Repository 作为数据访问的统一入口
    // 在此处可以集中处理安全相关的逻辑，如：
    // - 验证用户权限后再执行操作
    // - 对敏感字段进行脱敏处理后再返回给 UI 层
    // - 记录安全审计日志
 
    suspend fun getUser(userId: Long): User? {
        // 安全检查：在返回用户数据前，验证调用者是否有权访问
        val callerUid = android.os.Process.myUid()
        if (!hasAccessPermission(callerUid, userId)) {
            // 记录未授权访问尝试
            logSecurityEvent("Unauthorized access attempt: uid=$callerUid to user=$userId")
            return null
        }
 
        val user = userDao.getUserById(userId)
        // 脱敏处理：即使有访问权限，也不应将某些敏感字段直接暴露给 UI
        return user?.copy(
            // 例如：即使柜员可以查询客户，也不应看到完整的身份证号
            // 保留前三位和后四位，中间用星号替代
            idNumber = maskSensitiveField(user.idNumber),
            phoneNumber = maskSensitiveField(user.phoneNumber)
        )
    }
 
    private fun maskSensitiveField(value: String): String {
        if (value.length <= 7) return "****"
        return value.substring(0, 3) + "****" + value.substring(value.length - 4)
    }
}

-- sql
-- MySQL 安全审计查询：识别权限过宽的账户
-- 查找持有 DELETE 或 DROP 权限的非管理员账户
SELECT
    u.user,
    u.host,
    -- 将用户的权限汇总为一个易于阅读的列表
    GROUP_CONCAT(DISTINCT
        CASE
            WHEN p.db IS NULL AND p.table_name IS NULL THEN CONCAT('GLOBAL:', p Select_priv)
            WHEN p.table_name IS NOT NULL THEN CONCAT(p.db, '.', p.table_name, ':',
                IF(p.select_priv='Y','SELECT,',''),
                IF(p.insert_priv='Y','INSERT,',''),
                IF(p.update_priv='Y','UPDATE,',''),
                IF(p.delete_priv='Y','DELETE,',''),
                IF(p.drop_priv='Y','DROP,',''),
                IF(p.grant_priv='Y','GRANT,','')
            )
            ELSE CONCAT(p.db, ':',
                IF(p.select_priv='Y','SELECT,',''),
                IF(p.insert_priv='Y','INSERT,',''),
                IF(p.update_priv='Y','UPDATE,',''),
                IF(p.delete_priv='Y','DELETE,',''),
                IF(p.drop_priv='Y','DROP,',''),
                IF(p.grant_priv='Y','GRANT,','')
            )
        END
    ) AS privilege_summary
FROM mysql.user u
LEFT JOIN mysql.db p ON u.user = p.user AND u.host = p.host
WHERE u.user NOT IN ('root', 'mysql.infoschema', 'mysql.session', 'mysql.sys')
AND (u.delete_priv = 'Y' OR u.drop_priv = 'Y' OR u.grant_priv = 'Y'
     OR EXISTS (SELECT 1 FROM mysql.db pd WHERE pd.user = u.user
                AND (pd.delete_priv = 'Y' OR pd.drop_priv = 'Y')))
GROUP BY u.user, u.host;

// java
// 不安全的代码示例：直接拼接用户输入到 SQL 语句中
// 攻击者输入 "1; DROP TABLE users; --" 可能导致灾难性后果
public void searchUsers(String keyword) {
    // 危险！keyword 被直接拼接到 SQL 中
    String sql = "SELECT * FROM users WHERE name LIKE '%" + keyword + "%'";
    // 即使当前用户只有 SELECT 权限，
    // 某些数据库配置下注入的 DROP TABLE 仍可能被执行
    // 更常见的是通过 UNION 注入获取本无权限访问的表的数据
}
 
// 安全的代码示例：使用参数化查询
public void searchUsersSafe(String keyword) {
    // 参数化查询（PreparedStatement）确保用户输入被当作数据而非 SQL 代码
    String sql = "SELECT * FROM users WHERE name LIKE ?";
    PreparedStatement stmt = connection.prepareStatement(sql);
    stmt.setString(1, "%" + keyword + "%");
    ResultSet rs = stmt.executeQuery();
    // 输入中的任何 SQL 特殊字符（', ", ;, -- 等）都不会改变查询结构
}

CREATE TABLE Student (
    -- 学号作为主键，使用列级约束语法
    StudentID CHAR(10) PRIMARY KEY,
    StudentName VARCHAR(50) NOT NULL,
    Gender CHAR(1),
    BirthDate DATE,
    Major VARCHAR(50)
);

CREATE TABLE SC (
    -- 成绩关系中，学号和课程号共同构成复合主键
    -- 单独看学号或课程号都不能唯一确定一条选课记录
    StudentID CHAR(10),
    CourseID CHAR(6),
    Grade DECIMAL(5,1),
    PRIMARY KEY (StudentID, CourseID)  -- 表级复合主键约束
);

-- 假设原表没有主键，现在要将以 Email 列作为主键
ALTER TABLE Teacher
ADD CONSTRAINT PK_Teacher PRIMARY KEY (Email);

Student(SID, SName, Major)
Course(CID, CName, Credit)
SC(SID, CID, Grade)

-- 定义学生表（被参照表/父表）
CREATE TABLE Student (
    SID CHAR(10) PRIMARY KEY,
    SName VARCHAR(50) NOT NULL,
    Major VARCHAR(50)
);
 
-- 定义课程表（被参照表/父表）
CREATE TABLE Course (
    CID CHAR(6) PRIMARY KEY,
    CName VARCHAR(100) NOT NULL,
    Credit INT
);
 
-- 定义选课表（参照表/子表）
CREATE TABLE SC (
    SID CHAR(10),
    CID CHAR(6),
    Grade DECIMAL(4,1),
    PRIMARY KEY (SID, CID),
    -- 声明外键约束：指明 SID 列参照 Student 表的 SID 主键
    FOREIGN KEY (SID) REFERENCES Student(SID),
    -- 声明外键约束：指明 CID 列参照 Course 表的 CID 主键
    FOREIGN KEY (CID) REFERENCES Course(CID)
);

CREATE TABLE SC (
    SID CHAR(10),
    CID CHAR(6),
    Grade DECIMAL(4,1),
    PRIMARY KEY (SID, CID),
    CONSTRAINT FK_SC_Student FOREIGN KEY (SID) REFERENCES Student(SID),
    CONSTRAINT FK_SC_Course FOREIGN KEY (CID) REFERENCES Course(CID)
);

-- 尝试删除一个仍有选课记录的学生
DELETE FROM Student WHERE SID = '20230001';
-- 如果 SC 表中仍有该学号的记录，DBMS 将拒绝删除并报错
-- 错误信息类似：Cannot delete or update a parent row: a foreign key constraint fails

-- 定义外键约束时指定 ON DELETE CASCADE
CREATE TABLE SC (
    SID CHAR(10),
    CID CHAR(6),
    Grade DECIMAL(4,1),
    PRIMARY KEY (SID, CID),
    FOREIGN KEY (SID) REFERENCES Student(SID)
        ON DELETE CASCADE  -- 删除学生时，级联删除其所有选课记录
        ON UPDATE CASCADE  -- 修改学号时，级联更新所有选课记录中的学号
);

CREATE TABLE SC (
    SID CHAR(10),
    CID CHAR(6),
    Grade DECIMAL(4,1),
    PRIMARY KEY (SID, CID),
    FOREIGN KEY (SID) REFERENCES Student(SID)
        ON DELETE SET NULL  -- 学生被删除后，选课记录中学号设为 NULL
);

CREATE TABLE SC (
    SID CHAR(10) DEFAULT 'UNKNOWN',
    CID CHAR(6),
    Grade DECIMAL(4,1),
    PRIMARY KEY (SID, CID),
    FOREIGN KEY (SID) REFERENCES Student(SID)
        ON DELETE SET DEFAULT  -- 学生被删除后，学号设为默认值 'UNKNOWN'
);

CREATE TABLE Employee (
    EMP_ID CHAR(8) PRIMARY KEY,
    EMP_NAME VARCHAR(50) NOT NULL,
    MANAGER_ID CHAR(8),  -- 上级经理的工号
    TITLE VARCHAR(30),
    -- 自参照外键：MANAGER_ID 参照同一张表的 EMP_ID
    FOREIGN KEY (MANAGER_ID) REFERENCES Employee(EMP_ID)
        ON DELETE SET NULL   -- 经理离职时，将其设为 NULL（表示暂无上级）
        ON UPDATE CASCADE    -- 经理工号变更时，级联更新下属的 MANAGER_ID
);

CREATE TABLE Student (
    SID CHAR(10) PRIMARY KEY,
    SName VARCHAR(50) NOT NULL,
    Age INT,
    -- 年龄必须在 15 到 45 岁之间
    CONSTRAINT CK_Student_Age CHECK (Age >= 15 AND Age <= 45),
    Gender CHAR(1),
    -- 性别只能是 'M' 或 'F'
    CONSTRAINT CK_Student_Gender CHECK (Gender IN ('M', 'F'))
);

CREATE TABLE Teacher (
    TID CHAR(8) PRIMARY KEY,
    TName VARCHAR(50) NOT NULL,
    Title VARCHAR(20),
    -- 职称只能是以下四种之一
    CONSTRAINT CK_Teacher_Title
        CHECK (Title IN ('教授', '副教授', '讲师', '助教'))
);

CREATE TABLE Student (
    SID CHAR(10) PRIMARY KEY,
    Email VARCHAR(100),
    -- 邮箱地址必须包含 '@' 符号
    CONSTRAINT CK_Student_Email CHECK (Email LIKE '%@%.%'),
 
    PhoneNumber CHAR(11),
    -- 手机号必须是 11 位纯数字
    CONSTRAINT CK_Student_Phone CHECK (PhoneNumber NOT LIKE '%[^0-9]%'
                                       AND LENGTH(PhoneNumber) = 11)
);

CREATE TABLE OrderInfo (
    OrderID CHAR(12) PRIMARY KEY,
    OrderDate DATE NOT NULL,
    ShipDate DATE,
    -- 发货日期必须不早于订单日期
    CONSTRAINT CK_Order_ShipDate CHECK (ShipDate IS NULL OR ShipDate >= OrderDate),
 
    TotalAmount DECIMAL(10,2),
    PaidAmount DECIMAL(10,2),
    -- 已支付金额不能超过订单总金额
    CONSTRAINT CK_Order_PaidAmount CHECK (PaidAmount <= TotalAmount)
);

CREATE TABLE CourseEnrollment (
    StudentID CHAR(10) NOT NULL,
    CourseID CHAR(6) NOT NULL,
    EnrollmentYear INT,
    Semester INT,
    Grade DECIMAL(4,1),
    -- 复合主键：每个学生每门课只能有一条选课记录
    PRIMARY KEY (StudentID, CourseID),
    -- 学期编号必须是 1 或 2
    CONSTRAINT CK_Enrollment_Semester CHECK (Semester IN (1, 2)),
    -- 成绩只能在 0-100 之间，或者为空（表示尚未评分）
    CONSTRAINT CK_Enrollment_Grade CHECK (Grade IS NULL OR (Grade >= 0 AND Grade <= 100))
);

-- 将外键约束设为 INITIALLY DEFERRED，意味着在事务提交时才检查
CREATE TABLE SC (
    SID CHAR(10),
    CID CHAR(6),
    Grade DECIMAL(4,1),
    PRIMARY KEY (SID, CID),
    CONSTRAINT FK_SC_Student FOREIGN KEY (SID) REFERENCES Student(SID)
        ON DELETE CASCADE
        DEFERRABLE INITIALLY DEFERRED  -- 延迟检查：在 COMMIT 时才验证
);

// Android/Kotlin - 在 Repository 层处理约束违反异常
try {
    // 尝试插入一条新的学生记录
    studentDao.insert(student)
} catch (e: SQLiteConstraintException) {
    // 检测到完整性约束违反（如主键冲突或外键冲突）
    Log.e("DatabaseError", "数据完整性约束被违反: ${e.message}")
    // 根据业务需求决定如何处理：
    // 方案一：向用户显示友好的错误提示
    // 方案二：自动修正（如生成新的主键值后重试）
    // 方案三：记录日志并回滚事务
}

-- Oracle PL/SQL 示例：当订单总额超过 10000 时，自动将优先级设为 'HIGH'
CREATE OR REPLACE TRIGGER trg_set_priority
BEFORE INSERT OR UPDATE OF TotalAmount ON OrderInfo
FOR EACH ROW
WHEN (NEW.TotalAmount > 10000)
BEGIN
    :NEW.Priority := 'HIGH';
END;
/

-- SQL 示例：在订单表上创建一个触发器，当订单金额超过10000时自动记录日志
-- sql
CREATE TRIGGER log_high_value_orders
AFTER INSERT ON orders
BEGIN
    -- 判断新插入的订单金额是否超过10000元
    -- NEW.order_amount 引用新插入记录的金额字段
    -- 如果超过阈值，则在审计表中插入一条记录
    -- NEW.order_id 引用新订单的ID，NEW.order_amount 引用新订单的金额
    INSERT INTO order_audit (order_id, amount, audit_type, created_at)
    VALUES (NEW.order_id, NEW.order_amount, 'HIGH_VALUE', datetime('now'));
END;

-- sql
CREATE TRIGGER [IF NOT EXISTS] trigger_name
    [BEFORE | AFTER | INSTEAD OF]  -- 触发时机：事件之前、之后、还是替代原操作
    [INSERT | UPDATE | DELETE | UPDATE OF column_list]  -- 触发事件类型
    ON table_name                   -- 关联的目标表
    [FOR EACH ROW]                  -- 行级触发器标志（SQLite默认即是）
    [WHEN condition]                -- 触发条件（可选）
BEGIN
    trigger_statements;             -- 触发动作的主体语句
END;

-- sql
-- 示例1：创建一个防止非法工资调整的触发器
-- 在员工工资被更新之前检查新工资是否低于当前工资的80%
-- OLD.salary 引用更新前的原工资
-- NEW.salary 引用更新后的新工资
CREATE TRIGGER prevent_salary_reduction
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
WHEN NEW.salary < OLD.salary * 0.8
BEGIN
    -- 抛出异常信息，阻止此次更新操作
    -- SQLite没有内置的RAISE函数，但可以结合其他机制
    SELECT RAISE(ABORT, '工资下调幅度不能超过20%');
END;
 
-- 示例2：创建一个维护产品库存同步的触发器
-- 当订单详情被插入时，自动减少对应产品的库存数量
CREATE TRIGGER update_inventory_on_order
AFTER INSERT ON order_items
FOR EACH ROW
WHEN NEW.quantity IS NOT NULL AND NEW.quantity > 0
BEGIN
    -- 从产品库存表中减去已订购的数量
    -- 这里使用子查询来确保原子性操作
    UPDATE products
    -- 减去订购数量：NEW.quantity 引用订单详情中的订购数量
    -- WHERE条件确保只更新对应的产品
    SET stock_quantity = stock_quantity - NEW.quantity
    WHERE product_id = NEW.product_id;
END;
 
-- 示例3：创建一个记录所有数据变更的审计触发器
-- 支持INSERT、UPDATE、DELETE三种操作的统一审计表
CREATE TRIGGER audit_employees_changes
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
    -- 使用CASE语句区分不同操作类型
    -- INSERT操作时 OLD 为 NULL，NEW 包含新数据
    -- DELETE操作时 NEW 为 NULL，OLD 包含被删除的数据
    -- UPDATE操作时两者都有效
    INSERT INTO audit_log (
        table_name,
        operation,
        record_id,
        old_data,
        new_data,
        change_time
    ) VALUES (
        'employees',
        CASE
            -- 使用临时判断语句来检测当前触发的事件类型
            -- 这些条件在WHEN子句未覆盖的情况下仍然需要在代码中处理
            WHEN (SELECT 1 FROM pragma_function_list WHERE name = 'last_insert_rowid') IS NOT NULL
            THEN 'INSERT'
            ELSE 'MODIFY'
        END,
        COALESCE(NEW.employee_id, OLD.employee_id),
        CASE WHEN OLD.employee_id IS NOT NULL THEN json(OLD.*) ELSE NULL END,
        CASE WHEN NEW.employee_id IS NOT NULL THEN json(NEW.*) ELSE NULL END,
        datetime('now')
    );
END;

-- sql
-- 删除触发器的基本语法
-- DROP TRIGGER [IF EXISTS] trigger_name;
 
-- 示例：删除之前创建的触发器
DROP TRIGGER IF EXISTS prevent_salary_reduction;
 
-- 删除后重新创建一个更宽松的版本
-- 例如将工资下调容忍度从80%调整到70%
CREATE TRIGGER prevent_salary_reduction_v2
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
WHEN NEW.salary < OLD.salary * 0.7
BEGIN
    SELECT RAISE(ABORT, '工资下调幅度不能超过30%');
END;

-- sql
-- 方法1：通过sqlite_master系统表查询所有触发器
-- sqlite_master是SQLite的元数据表，存储了数据库的结构信息
SELECT name, sql FROM sqlite_master
WHERE type = 'trigger'
ORDER BY name;
 
-- 方法2：查询特定表的所有触发器
-- 仅显示与指定表相关的触发器
SELECT name, sql FROM sqlite_master
WHERE type = 'trigger'
  AND tbl_name = 'employees'
ORDER BY name;
 
-- 方法3：使用PRAGMA语句查询更详细的信息
-- pragma_trigger_info可以获取触发器的详细元数据
PRAGMA table_info('employees');  -- 先查看表结构
 
-- 方法4：获取触发器关联的表和事件
SELECT
    name AS trigger_name,
    tbl_name AS table_name,
    sql AS definition
FROM sqlite_master
WHERE type = 'trigger'
  AND sql LIKE '%INSERT%';  -- 查找所有INSERT相关的触发器

-- sql
-- SQLite特有：创建视图上的INSTEAD OF触发器
-- 这在SQLite中非常有用，因为SQLite的视图是只读的
-- 通过INSTEAD OF触发器可以向视图写入数据
 
-- 首先创建一个客户订单汇总视图
CREATE VIEW customer_order_summary AS
SELECT
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(o.order_amount), 0) AS total_amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;
 
-- 为该视图创建一个INSTEAD OF INSERT触发器
-- 当向视图插入数据时，自动将数据路由到相应的基础表
CREATE TRIGGER instead_of_insert_order_summary
INSTEAD OF INSERT ON customer_order_summary
FOR EACH ROW
BEGIN
    -- 插入新客户到客户表
    -- 假设视图插入时提供了客户ID和客户名称
    INSERT OR IGNORE INTO customers (customer_id, customer_name)
    VALUES (NEW.customer_id, NEW.customer_name);
END;

// kotlin
// Android Room中如何创建和管理触发器
 
// 方式1：使用Room的Callback在数据库创建时执行触发器创建语句
// 这是最常用的方法，在数据库第一次被创建时自动执行DDL语句
 
@Database(
    entities = [Employee::class, AuditLog::class],
    version = 1,
    exportSchema = false
)
abstract class AppDatabase : RoomDatabase() {
 
    abstract fun employeeDao(): EmployeeDao
 
    // 使用Callback在数据库创建时初始化触发器
    companion object {
        @Volatile
        private var INSTANCE: AppDatabase? = null
 
        fun getDatabase(context: Context): AppDatabase {
            return INSTANCE ?: synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    AppDatabase::class.java,
                    "company_database"
                )
                    // 添加数据库创建回调
                    .addCallback(DatabaseCallback())
                    .build()
                INSTANCE = instance
                instance
            }
        }
    }
 
    /**
     * 数据库创建回调类
     * 当数据库首次创建或版本升级时，onCreate方法会被调用
     * 我们可以在这个时机创建所有需要的触发器
     */
    private class DatabaseCallback : Callback() {
        override fun onCreate(db: SupportSQLiteDatabase) {
            super.onCreate(db)
            // 在数据库创建时执行触发器创建语句
            db.execSQL("""
                -- 创建员工表审计触发器
                -- 当员工表发生插入、更新或删除操作时，自动记录审计日志
                CREATE TRIGGER IF NOT EXISTS trg_employee_audit_insert
                AFTER INSERT ON employees
                FOR EACH ROW
                BEGIN
                    -- 将插入操作记录到审计表
                    -- NEW.employee_id 等引用新插入记录的值
                    INSERT INTO audit_log (
                        table_name,
                        operation,
                        record_id,
                        old_value,
                        new_value,
                        timestamp
                    ) VALUES (
                        'employees',
                        'INSERT',
                        NEW.employee_id,
                        NULL,
                        json_object(
                            'name', NEW.name,
                            'department', NEW.department,
                            'salary', NEW.salary
                        ),
                        datetime('now')
                    );
                END;
            """.trimIndent())
 
            db.execSQL("""
                -- 创建员工表更新审计触发器
                -- 记录所有字段的变更（通过OLD和NEW对比）
                CREATE TRIGGER IF NOT EXISTS trg_employee_audit_update
                AFTER UPDATE ON employees
                FOR EACH ROW
                WHEN OLD.name != NEW.name
                   OR OLD.department != NEW.department
                   OR OLD.salary != NEW.salary
                BEGIN
                    INSERT INTO audit_log (
                        table_name,
                        operation,
                        record_id,
                        old_value,
                        new_value,
                        timestamp
                    ) VALUES (
                        'employees',
                        'UPDATE',
                        NEW.employee_id,
                        json_object(
                            'name', OLD.name,
                            'department', OLD.department,
                            'salary', OLD.salary
                        ),
                        json_object(
                            'name', NEW.name,
                            'department', NEW.department,
                            'salary', NEW.salary
                        ),
                        datetime('now')
                    );
                END;
            """.trimIndent())
 
            db.execSQL("""
                -- 创建员工表删除审计触发器
                -- 在删除前保存被删除的数据（因为DELETE后OLD数据不可直接引用）
                CREATE TRIGGER IF NOT EXISTS trg_employee_audit_delete
                AFTER DELETE ON employees
                FOR EACH ROW
                BEGIN
                    INSERT INTO audit_log (
                        table_name,
                        operation,
                        record_id,
                        old_value,
                        new_value,
                        timestamp
                    ) VALUES (
                        'employees',
                        'DELETE',
                        OLD.employee_id,
                        json_object(
                            'name', OLD.name,
                            'department', OLD.department,
                            'salary', OLD.salary
                        ),
                        NULL,
                        datetime('now')
                    );
                END;
            """.trimIndent())
        }
    }
}

// kotlin
// 方式2：使用Room的Migration（迁移）来添加触发器
// 适用于数据库版本升级时需要添加新的触发器
 
// 首先定义迁移版本2
val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // 在版本升级时创建触发器
        // 这确保了已存在的数据库也能获得新触发器
        database.execSQL("""
            CREATE TRIGGER IF NOT EXISTS trg_auto_archive_orders
            AFTER UPDATE ON orders
            FOR EACH ROW
            WHEN NEW.status = 'COMPLETED'
            BEGIN
                -- 当订单状态变更为已完成时，自动将其归档
                -- 将完成日期设置为当前时间
                -- 这里使用延迟归档，实际移动数据到归档表
                INSERT INTO orders_archive (
                    order_id, customer_id, order_date,
                    completed_date, total_amount
                )
                VALUES (
                    NEW.order_id,
                    NEW.customer_id,
                    NEW.order_date,
                    datetime('now'),
                    NEW.total_amount
                );
            END;
        """.trimIndent())
    }
}
 
// 在构建数据库时应用迁移
val instance = Room.databaseBuilder(
    context.applicationContext,
    AppDatabase::class.java,
    "company_database"
)
    .addMigrations(MIGRATION_1_2)
    .build()

// kotlin
// 场景实践：使用触发器实现订单状态变更的实时通知
// 在触发器中设置标志位，应用程序轮询或观察该标志位来触发通知
 
// 首先创建一个通知事件表，用于触发器写入事件
@Database(entities = [NotificationEvent::class], version = 1)
abstract class NotificationDatabase : RoomDatabase() {
    abstract fun notificationEventDao(): NotificationEventDao
}
 
// 定义通知事件实体类
@Entity(tableName = "notification_events")
data class NotificationEvent(
    @PrimaryKey(autoGenerate = true)
    val id: Long = 0,
    val eventType: String,        // 事件类型，如ORDER_STATUS_CHANGE
    val relatedId: Long,          // 关联记录ID
    val message: String,          // 通知消息内容
    val isProcessed: Boolean = false,  // 处理标志，程序处理后设为true
    val createdAt: String         // 创建时间
)
 
// 创建触发器监听订单状态变更
db.execSQL("""
    CREATE TRIGGER IF NOT EXISTS trg_order_status_notification
    AFTER UPDATE OF status ON orders
    FOR EACH ROW
    WHEN OLD.status != NEW.status
    BEGIN
        -- 当订单状态发生变化时，向通知事件表插入记录
        -- 应用程序可以观察这张表来获取实时通知
        INSERT INTO notification_events (
            event_type,
            related_id,
            message,
            is_processed,
            created_at
        ) VALUES (
            'ORDER_STATUS_CHANGE',
            NEW.order_id,
            printf('订单 %d 状态已从 [%s] 更新为 [%s]',
                   NEW.order_id, OLD.status, NEW.status),
            0,
            datetime('now')
        );
    END;
""".trimIndent())
 
// 应用程序端监听通知事件的示例
// 使用Flow持续监听未处理的notification_events表
@Dao
interface NotificationEventDao {
    @Query("""
        SELECT * FROM notification_events
        WHERE is_processed = 0
        ORDER BY created_at DESC
    """)
    fun getUnprocessedNotifications(): Flow<List<NotificationEvent>>
 
    @Query("""
        UPDATE notification_events
        SET is_processed = 1
        WHERE id = :eventId
    """)
    suspend fun markAsProcessed(eventId: Long)
}

-- sql
-- 综合业务规则验证触发器示例
 
-- 场景1：订单状态流转规则验证
-- 订单状态流转图：PENDING -> PROCESSING -> SHIPPED -> COMPLETED
--                   |                     |
--                   +-------- CANCELLED <--+
-- 只有满足上述规则的状态转换才被允许
CREATE TRIGGER trg_validate_order_status_transition
BEFORE UPDATE OF status ON orders
FOR EACH ROW
WHEN OLD.status != NEW.status
BEGIN
    SELECT CASE
        -- 规则1：已完成的订单不能取消
        WHEN OLD.status = 'COMPLETED'
        THEN RAISE(ABORT, '已完成的订单不能修改状态')
        -- 规则2：已取消的订单不能再变更状态
        WHEN OLD.status = 'CANCELLED'
        THEN RAISE(ABORT, '已取消的订单不能重新激活')
        -- 规则3：PENDING状态下只能变更为PROCESSING或CANCELLED
        WHEN OLD.status = 'PENDING'
             AND NEW.status NOT IN ('PROCESSING', 'CANCELLED')
        THEN RAISE(ABORT, '待处理订单只能转为处理中或已取消')
        -- 规则4：PROCESSING状态下只能变更为SHIPPED或CANCELLED
        WHEN OLD.status = 'PROCESSING'
             AND NEW.status NOT IN ('SHIPPED', 'CANCELLED')
        THEN RAISE(ABORT, '处理中订单只能转为已发货或已取消')
        -- 规则5：SHIPPED状态下只能变更为COMPLETED
        WHEN OLD.status = 'SHIPPED'
             AND NEW.status != 'COMPLETED'
        THEN RAISE(ABORT, '已发货订单只能标记为已完成')
    END;
END;
 
-- 场景2：员工调薪幅度限制
-- 每次调薪幅度不能超过原工资的30%
CREATE TRIGGER trg_validate_salary_adjustment
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
WHEN OLD.salary != NEW.salary
BEGIN
    SELECT CASE
        -- 检查是否为降薪且幅度超过30%
        -- OLD.salary * 1.3 表示原工资的130%，即允许的最大涨幅
        -- 如果新工资低于这个值，说明降薪幅度超过30%
        WHEN NEW.salary < OLD.salary * 0.7
        THEN RAISE(ABORT, '工资下调幅度不能超过原工资的30%')
        -- 检查是否为涨薪且幅度超过30%
        WHEN NEW.salary > OLD.salary * 1.3
        THEN RAISE(ABORT, '工资上调幅度不能超过原工资的30%，请提交特殊审批')
        -- 检查是否为负数工资
        WHEN NEW.salary < 0
        THEN RAISE(ABORT, '工资不能为负数')
    END;
END;

-- sql
-- 级联操作示例：维护订单与账户余额的一致性
 
-- 账户表，记录每个账户的余额
CREATE TABLE accounts (
    account_id INTEGER PRIMARY KEY,
    account_name TEXT NOT NULL,
    balance REAL NOT NULL DEFAULT 0
);
 
-- 交易明细表，记录每一笔收入和支出
CREATE TABLE transactions (
    transaction_id INTEGER PRIMARY KEY,
    account_id INTEGER NOT NULL,
    amount REAL NOT NULL,
    transaction_type TEXT NOT NULL CHECK(transaction_type IN ('DEPOSIT', 'WITHDRAW')),
    transaction_date TEXT NOT NULL,
    description TEXT,
    FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);
 
-- 当交易被插入时，自动更新账户余额
CREATE TRIGGER trg_update_balance_on_insert
AFTER INSERT ON transactions
FOR EACH ROW
WHEN NEW.amount > 0
BEGIN
    -- 根据交易类型增减余额
    -- DEPOSIT表示存款，余额增加；WITHDRAW表示取款，余额减少
    UPDATE accounts
    SET balance = balance +
        CASE
            WHEN NEW.transaction_type = 'DEPOSIT' THEN NEW.amount
            WHEN NEW.transaction_type = 'WITHDRAW' THEN -NEW.amount
            ELSE 0
        END
    WHERE account_id = NEW.account_id;
END;
 
-- 当交易被删除时，反向冲销账户余额变更
-- 类似于“撤销”操作的效果
CREATE TRIGGER trg_reverse_balance_on_delete
AFTER DELETE ON transactions
FOR EACH ROW
WHEN OLD.amount > 0
BEGIN
    UPDATE accounts
    SET balance = balance +
        CASE
            WHEN OLD.transaction_type = 'DEPOSIT' THEN -OLD.amount
            WHEN OLD.transaction_type = 'WITHDRAW' THEN OLD.amount
            ELSE 0
        END
    WHERE account_id = OLD.account_id;
END;

-- sql
-- 审计日志表的设计
CREATE TABLE audit_log (
    audit_id INTEGER PRIMARY KEY AUTOINCREMENT,
    table_name TEXT NOT NULL,           -- 被操作的表名
    operation TEXT NOT NULL,             -- 操作类型：INSERT、UPDATE、DELETE
    record_id TEXT,                      -- 被操作记录的主键值
    old_values TEXT,                     -- 变更前的完整数据（JSON格式）
    new_values TEXT,                     -- 变更后的完整数据（JSON格式）
    changed_columns TEXT,                -- 实际变更的列名列表
    old_checksum TEXT,                   -- 变更前数据的校验和
    new_checksum TEXT,                   -- 变更后数据的校验和
    session_user TEXT,                   -- 执行操作的用户
    application_name TEXT,              -- 发起操作的应用程序
    client_ip TEXT,                      -- 客户端IP地址
    server_timestamp TEXT DEFAULT (datetime('now', 'utc')),  -- 服务器时间戳
    transaction_id INTEGER              -- 所属事务的ID
);
 
-- 创建综合审计触发器
-- 使用临时表存储变更列的检测结果
CREATE TRIGGER trg_audit_orders
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (
        table_name,
        operation,
        record_id,
        old_values,
        new_values,
        changed_columns,
        old_checksum,
        new_checksum,
        session_user,
        application_name,
        server_timestamp
    )
    VALUES (
        'orders',
        CASE
            WHEN INSERTING() THEN 'INSERT'
            WHEN UPDATING() THEN 'UPDATE'
            WHEN DELETING() THEN 'DELETE'
        END,
        COALESCE(NEW.order_id, OLD.order_id),
        CASE WHEN OLD.order_id IS NOT NULL
             THEN json_object(
                 'order_id', OLD.order_id,
                 'customer_id', OLD.customer_id,
                 'order_date', OLD.order_date,
                 'status', OLD.status,
                 'total_amount', OLD.total_amount
             ) ELSE NULL END,
        CASE WHEN NEW.order_id IS NOT NULL
             THEN json_object(
                 'order_id', NEW.order_id,
                 'customer_id', NEW.customer_id,
                 'order_date', NEW.order_date,
                 'status', NEW.status,
                 'total_amount', NEW.total_amount
             ) ELSE NULL END,
        CASE
            WHEN UPDATING() THEN json_array(
                CASE WHEN OLD.customer_id != NEW.customer_id THEN 'customer_id' END,
                CASE WHEN OLD.status != NEW.status THEN 'status' END,
                CASE WHEN OLD.total_amount != NEW.total_amount THEN 'total_amount' END
            )
            ELSE NULL
        END,
        CASE WHEN OLD.order_id IS NOT NULL
             THEN hex(sha512(OLD.order_id || OLD.customer_id || OLD.total_amount))
             ELSE NULL END,
        CASE WHEN NEW.order_id IS NOT NULL
             THEN hex(sha512(NEW.order_id || NEW.customer_id || NEW.total_amount))
             ELSE NULL END,
        'APP_USER',
        'CompanyApp-v2.0',
        datetime('now', 'utc')
    );
END;

-- sql
-- 自动计算派生数据示例：维护客户统计信息
 
-- 客户表
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name TEXT NOT NULL,
    customer_tier TEXT DEFAULT 'BRONZE',  -- 客户等级：BRONZE、SILVER、GOLD、PLATINUM
    total_orders INTEGER DEFAULT 0,       -- 累计订单数
    total_spent REAL DEFAULT 0,            -- 累计消费金额
    last_order_date TEXT                  -- 最后下单日期
);
 
-- 触发器1：当新订单创建时，更新客户的统计信息
CREATE TRIGGER trg_update_customer_stats_on_new_order
AFTER INSERT ON orders
FOR EACH ROW
WHEN NEW.status != 'CANCELLED'
BEGIN
    -- 使用原子更新方式增加统计计数
    -- 累计订单数加1
    UPDATE customers
    SET total_orders = total_orders + 1,
        -- 累计消费金额加上新订单金额
        total_spent = total_spent + NEW.total_amount,
        -- 更新最后下单日期
        last_order_date = NEW.order_date
    WHERE customer_id = NEW.customer_id;
 
    -- 同时根据累计消费自动调整客户等级
    -- 这个逻辑根据消费金额阈值自动升级客户等级
    UPDATE customers
    SET customer_tier =
        CASE
            -- 消费满50000升级为PLATINUM
            WHEN total_spent >= 50000 THEN 'PLATINUM'
            -- 消费满20000升级为GOLD
            WHEN total_spent >= 20000 THEN 'GOLD'
            -- 消费满5000升级为SILVER
            WHEN total_spent >= 5000 THEN 'SILVER'
            -- 其他为BRONZE
            ELSE 'BRONZE'
        END
    WHERE customer_id = NEW.customer_id;
END;
 
-- 触发器2：当订单取消时，逆向扣减统计（可选逻辑）
CREATE TRIGGER trg_revert_customer_stats_on_cancel
AFTER UPDATE ON orders
FOR EACH ROW
WHEN OLD.status != 'CANCELLED' AND NEW.status = 'CANCELLED'
BEGIN
    UPDATE customers
    SET total_orders = total_orders - 1,
        total_spent = total_spent - OLD.total_amount,
        -- 注意：last_order_date不会回退到倒数第二次订单
        -- 因为订单取消不代表订单从未存在
    WHERE customer_id = OLD.customer_id;
 
    -- 重新计算客户等级（因为消费金额减少可能导致降级）
    UPDATE customers
    SET customer_tier =
        CASE
            WHEN total_spent >= 50000 THEN 'PLATINUM'
            WHEN total_spent >= 20000 THEN 'GOLD'
            WHEN total_spent >= 5000 THEN 'SILVER'
            ELSE 'BRONZE'
        END
    WHERE customer_id = OLD.customer_id;
END;

-- sql
-- 使用约束 vs 触发器实现相同功能
 
-- 方法1：使用CHECK约束验证工资非负
-- 这是最简单、最高效的方式
ALTER TABLE employees
ADD CONSTRAINT chk_salary_non_negative
CHECK (salary >= 0);
 
-- 方法2：使用CHECK约束验证工资范围
-- CHECK约束非常适合简单的值范围检查
ALTER TABLE employees
ADD CONSTRAINT chk_salary_range
CHECK (salary BETWEEN 3000 AND 500000);
 
-- 方法3：使用触发器验证复杂的状态流转规则
-- 当业务规则涉及状态间的逻辑关系而非简单的值检查时
-- 触发器比CHECK约束更具表达力
CREATE TRIGGER trg_validate_order_status
BEFORE UPDATE ON orders
FOR EACH ROW
WHEN OLD.status = 'COMPLETED' AND NEW.status != 'COMPLETED'
BEGIN
    SELECT RAISE(ABORT, '已完成订单不能回退状态');
END;

-- 将 employees 表的查询权限授予 HR_Admin 角色
GRANT SELECT ON employees TO HR_Admin;
 
-- 将员工表中更新薪资的权限授予工资管理员
GRANT UPDATE(salary) ON employees TO payroll_admin;
 
-- 授予权限时附加 WITH GRANT OPTION，允许接收者再授予给他人
GRANT SELECT, INSERT ON departments TO dept_manager WITH GRANT OPTION;

-- 撤销 HR_Admin 角色对 employees 表的查询权限
REVOKE SELECT ON employees FROM HR_Admin;
 
-- 撤销 WITH GRANT OPTION 权限（同时撤销接收者转授的权限）
REVOKE GRANT OPTION FOR SELECT ON employees FROM dept_manager CASCADE;

-- 创建角色
CREATE ROLE data_analyst;
 
-- 为角色分配权限
GRANT SELECT ON sales_data TO data_analyst;
GRANT SELECT ON inventory TO data_analyst;
 
-- 将角色分配给用户
GRANT data_analyst TO user_zhang_san;
GRANT data_analyst TO user_li_si;

-- 定义主键约束，确保每个学生有唯一标识
CREATE TABLE students (
    student_id VARCHAR(20) PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    enrollment_date DATE NOT NULL
);

-- 定义外键约束，维护参照完整性
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

-- CHECK 约束实现用户定义完整性
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    stock_quantity INT NOT NULL CHECK (stock_quantity >= 0),
    discount_rate DECIMAL(3, 2) CHECK (discount_rate BETWEEN 0 AND 1)
);

-- Oracle 风格的触发器：自动维护员工薪资变更审计日志
CREATE OR REPLACE TRIGGER trg_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary != OLD.salary)
DECLARE
    v_change_amount NUMBER(10, 2);
BEGIN
    -- 计算薪资变更量
    v_change_amount := :NEW.salary - :OLD.salary;
 
    -- 插入审计日志记录
    INSERT INTO salary_audit (
        audit_id,
        employee_id,
        old_salary,
        new_salary,
        change_amount,
        change_date,
        changed_by
    ) VALUES (
        salary_audit_seq.NEXTVAL,
        :OLD.employee_id,
        :OLD.salary,
        :NEW.salary,
        v_change_amount,
        SYSDATE,
        USER
    );
END;
/

-- MySQL 风格的触发器：自动更新库存数量
DELIMITER //
CREATE TRIGGER trg_after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    -- 检查库存是否充足
    IF (SELECT stock_quantity FROM products
        WHERE product_id = NEW.product_id) < NEW.quantity THEN
        -- 抛出错误（MySQL 5.5 及之前版本需要特殊处理）
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '库存不足，无法创建订单';
    ELSE
        -- 扣减库存
        UPDATE products
        SET stock_quantity = stock_quantity - NEW.quantity
        WHERE product_id = NEW.product_id;
    END IF;
END //
DELIMITER ;

@Entity(
    tableName = "orders",
    foreignKeys = [
        ForeignKey(
            entity = Customer::class,
            parentColumns = ["customer_id"],
            childColumns = ["customer_id"],
            onDelete = ForeignKey.RESTRICT,
            onUpdate = ForeignKey.CASCADE
        )
    ],
    indices = [Index("customer_id")]
)
data class Order(
    @PrimaryKey
    @ColumnInfo(name = "order_id")
    val orderId: Int,
 
    @ColumnInfo(name = "customer_id")
    val customerId: Int,
 
    @ColumnInfo(name = "order_date")
    val orderDate: Long, // 使用时间戳存储
 
    @ColumnInfo(name = "quantity")
    val quantity: Int
)