上一篇学习了视图的使用,对于数据库,还有很多需要我们了解的。 触发器,是干什么的呢?怎么用呢?用它有什么好处呢?肯定也会有弊端等等一系列的问题,学习SQL Server的人一定多会对它有所了解的。 我的理解: 当给他一个事件时,给以同时完成另一个事件。
上一篇学习了视图的使用,对于数据库,还有很多需要我们了解的。
触发器,是干什么的呢?怎么用呢?用它有什么好处呢?肯定也会有弊端等等一系列的问题,学习SQL Server的人一定多会对它有所了解的。
我的理解:
当给他一个事件时,给以同时完成另一个事件。机房重构里,当我对充值表进行充值时,给它一个Insert事件,使用触发器可以同时更细卡表里的余额。
1、干什么用的:
触发器是SQL Server提供给程序员和数据分析员来保证数据完整性的一种方式,它就是与表事件相关的特殊存储过程。触发器不能被直接执行,只能为表上的Insert\Update\Delete事件所触发。它也不能传递或接受参数。
2、怎么用:
已经介绍了触发器是为表上的增删改事件触发,所以要在需要触发器的表里创建。
以机房收费系统里的充值表为例
-- ============================================= -- Author: <赵亚盟> -- Create date: <2014年8月10日21:25:06> -- Description: <当插入一条充值记录时,自动更新卡表中的余额> -- ============================================= ALTER TRIGGER [dbo].[CardBalance] ON [dbo].[T_Recharge] for Insert AS declare @AddMoney varchar(8) declare @Balance numeric(18,0) declare @CardNo varchar(8) --声明参数 select @AddMoney =Addmoney from T_Recharge select @Balance =Balance from T_Card select @CardNo =CardNo from T_Recharge --给参数赋值 BEGIN UPDATE T_Card set Balance =@Balance +@AddMoney Where T_Card .CardNo =@CardNo END
对代码功能的解释:
我有两张表,充值表里存放充值记录,卡表里存放余额,当我充值时,对充值表插入一条记录,要同时更新卡表里的余额。现在卡表里的余额=上次余额+充值金额
3、使用触发器的好处:
1、触发器的开销非常低,运行触发器所占用的时间主要花在引用其它存于内存或磁盘上的表上。
2、通过数据库中的相关表实现级联更改。
3、可以实现比CHECK约束更为复杂的数据完整性。
弊端:
不管是什么都有它的缺点,触发器也是不能滥用的,他也有很多缺点,滥用触发器会造成数据库以及应用程序的维护困难,如果我们过分依赖触发器,一定会影响数据库的结构。
初次尝试使用触发器,感觉挺方便的,使代码减少了很多,触发器的使用在以后的学习中还得好好学习,数据库的强大之处还得慢慢挖掘。

Installing MySQL on Linux can be done through the package manager. The specific steps are as follows: 1. On Ubuntu, use apt to update the package list and install the MySQL server; 2. On CentOS, use yum to install the MySQL community version and start the service. After installation, basic configuration needs to be performed, such as setting the root password and creating database and users.

MySQL views are virtual tables generated based on SQL queries. 1. Create a view: Use the CREATEVIEW statement combined with SELECT query. 2. Usage scenarios: simplify complex queries, data abstraction and ensure data consistency. 3. Optimization strategy: simplify underlying queries, use indexes, and consider materialized views.

When creating a database in MySQL, character sets and collation rules should be specified to ensure data accuracy and improve query performance. 1) Use the CREATEDATABASEmy_databaseCHARACTERSETutf8mb4COLLATIONutf8mb4_unicode_ci command to create a database, select the utf8mb4 character set and utf8mb4_unicode_ci collation. 2) utf8mb4 supports more Unicode characters, while utf8mb4_unicode_ci provides case-insensitive comparisons. 3) Ensure that the application layer and database layer use the same character set and collation rules to avoid potential problems.

In MySQL, sorting uses the ORDERBY clause, and ranking uses the RANK(), DENSE_RANK(), and ROW_NUMBER() functions. 1. Sort: Use ORDERBY clause, such as SELECT*FROMemployeesORDERBYsalaryDESC; 2. Ranking: Use window functions, such as SELECTemployee_name, salary, RANK()OVER(ORDERBYsalaryDESC)ASrankFROMemployees; these operations are based on SQL query optimizer and execution engine, and are often used to sort quickly or merge sort, and ranking depends on window function calculation.

To create and call stored procedures in MySQL, follow the following steps: 1. Create stored procedures: Use the CREATEPROCEDURE statement to define stored procedures, including names, parameters, and SQL statements. 2. Compile stored procedures: MySQL compiles stored procedures into executable code and stores them. 3. Call stored procedure: use CALL statement and pass parameters. 4. Execute stored procedures: MySQL executes the SQL statements in it, processes parameters and returns the result.

The MySQL service can be set to automatically start on Windows, Linux, and macOS. 1) On Windows, use the command "scconfigmysqlstart=auto" to configure. 2) On Linux, enable it using "sudosystemctlenablemysql". 3) On macOS, create and load the launchd configuration file to achieve automatic startup.

The methods to view the MySQL table structure include: 1. Use the DESCRIBE command to view column information; 2. Use the SHOWCREATETABLE command to view table creation statements; 3. Use information_schema to query more detailed information. These methods help to quickly understand table structure and improve work efficiency.

Installing MySQL on macOS can be achieved through the following steps: 1. Install Homebrew, using the command /bin/bash-c"$(curl-fsSLhttps://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)". 2. Update Homebrew and use brewupdate. 3. Install MySQL and use brewinstallmysql. 4. Start MySQL service and use brewservicesstartmysql. After installation, you can use mysql-u


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Atom editor mac version download
The most popular open source editor

DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

Zend Studio 13.0.1
Powerful PHP integrated development environment
