The creation and use of mysql triggers

**What is a Trigger?** A trigger (TRIGGER) is one of the database objects supported by MySQL, introduced in version 5.0.2. It functions similarly to functions in programming languages, requiring declaration and execution. However, unlike regular functions, triggers are not called directly by programs or manually initiated. Instead, they are automatically activated when specific events occur, such as INSERT, UPDATE, or DELETE operations on a table. This behavior is similar to event listeners in web development, like those in the DOM. Why use database triggers? When working on real-world projects, you often encounter scenarios where certain actions need to be performed automatically upon changes in the database. For example: 1. In a student table, there may be a field that keeps track of the total number of students. When adding new student information, this count should update automatically. 2. Fields like phone numbers or email addresses might require validation when inserting data into the student table. Triggers allow these tasks to be handled automatically. Whenever an INSERT, UPDATE, or DELETE operation occurs, MySQL will execute the associated trigger, making it ideal for maintaining consistency and enforcing business rules.  **MySQL Overview** MySQL is a relational database management system (RDBMS) developed by MySQL AB in Sweden and now owned by Oracle. It's one of the most popular RDBMS solutions, especially for web applications. MySQL organizes data in tables rather than storing everything in a single large repository, which improves performance and flexibility. SQL is the standard language used to interact with databases, and MySQL supports this widely accepted syntax. The software is available in both community and commercial versions, with the open-source community edition being particularly popular among small and medium-sized websites due to its low cost, fast performance, and ease of use. Thanks to its robust community support, MySQL pairs well with PHP and Apache, creating a powerful and widely used development stack. **Creating a Trigger** To create a trigger, you can use the `CREATE TRIGGER` statement. The basic syntax is: ```sql CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body ``` - **trigger_name**: A unique name for the trigger. - **trigger_time**: Either BEFORE or AFTER, indicating when the trigger should fire. - **trigger_event**: The event that activates the trigger (INSERT, UPDATE, or DELETE). - **tbl_name**: The table on which the trigger is defined. - **trigger_body**: The SQL statements executed when the trigger is fired. **Creating a Simple Trigger** Let’s walk through an example. Suppose we have two tables: `student_info` and `student_count`. We want to keep the total student count updated automatically. 1. **Create the tables**: ```sql CREATE TABLE student_info ( stu_no INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255), PRIMARY KEY (stu_no) ); CREATE TABLE student_count ( student_count INT(11) DEFAULT 0 ); INSERT INTO student_count VALUES(0); ``` 2. **Create the triggers**: ```sql CREATE TRIGGER trigger_student_count_insert AFTER INSERT ON student_info FOR EACH ROW UPDATE student_count SET student_count = student_count + 1; CREATE TRIGGER trigger_student_count_delete AFTER DELETE ON student_info FOR EACH ROW UPDATE student_count SET student_count = student_count - 1; ``` 3. **Test the triggers**: ```sql INSERT INTO student_info VALUES (NULL, 'Zhang Ming'), (NULL, 'Li Ming'), (NULL, 'Wang Ming'); SELECT * FROM student_count; -- Should return 3 DELETE FROM student_info WHERE stu_name IN ('Zhang Ming', 'Li Ming'); SELECT * FROM student_count; -- Should return 1 ``` This shows the trigger works correctly, updating the count based on insertions and deletions. **Creating a Trigger with Multiple Statements** If your trigger needs to run multiple SQL statements, you must enclose them in `BEGIN...END`. Example: Add a `student_class` field to track grade levels. 1. **Modify the tables**: ```sql ALTER TABLE student_info ADD COLUMN student_class INT; ``` 2. **Create the trigger**: ```sql DELIMITER $$ CREATE TRIGGER trigger_student_count_insert AFTER INSERT ON student_info FOR EACH ROW BEGIN UPDATE student_count SET student_count = student_count + 1 WHERE student_class = 0; UPDATE student_count SET student_count = student_count + 1 WHERE student_class = NEW.student_class; END $$ DELIMITER ; ``` Repeat a similar process for the DELETE trigger using `OLD.student_class`. **NEW and OLD Keywords** In triggers, `NEW` refers to the row being inserted, while `OLD` refers to the row being deleted. These keywords help access values from the current or previous state of the row. **Trigger Usage Restrictions** Some limitations apply to triggers: 1. Triggers can only be created on permanent tables, not temporary ones. 2. They cannot call stored procedures that return values or use dynamic SQL. 3. Triggers cannot start or commit transactions, though savepoints are allowed. 4. Foreign key operations do not activate triggers. 5. Row-based replication does not trigger slaves, but statement-based replication does. 6. Triggers cannot return values, so `RETURN` is not allowed. Use `LEAVE` to exit early. **Exception Handling in Triggers** Triggers execute in the order of BEFORE, then the operation, then AFTER. If any step fails, the entire operation is rolled back (for transactional tables), but not for non-transactional tables. **Viewing Triggers** You can view all triggers using `SHOW TRIGGERS`, but for more targeted queries, use the `information_schema.triggers` table: ```sql USE information_schema; SELECT * FROM triggers WHERE trigger_name = 'trigger_student_count_insert'; ``` **Deleting a Trigger** To remove a trigger, use: ```sql DROP TRIGGER trigger_name; ``` By understanding and using triggers effectively, developers can automate many database tasks, ensuring data integrity and reducing manual intervention.

Siren and Alarm

A siren is a loud noise-making device. Civil defense sirens are mounted in fixed locations and used to warn of natural disasters or attacks. Sirens are used on emergency service vehicles such as ambulances, police cars, and fire trucks. There are two general types: pneumatic and electronic.

Many fire sirens (used for calling the volunteer fire fighters) serve double duty as tornado or civil defense sirens, alerting an entire community of impending danger. Most fire sirens are either mounted on the roof of a fire station or on a pole next to the fire station. Fire sirens can also be mounted on or near government buildings, on tall structures such as water towers, as well as in systems where several sirens are distributed around a town for better sound coverage. Most fire sirens are single tone and mechanically driven by electric motors with a rotor attached to the shaft. Some newer sirens are electronically driven speakers.

Fire sirens are often called "fire whistles", "fire alarms", or "fire horns". Although there is no standard signaling of fire sirens, some utilize codes to inform firefighters of the location of the fire. Civil defense sirens also used as fire sirens often can produce an alternating "hi-lo" signal (similar to emergency vehicles in many European countries) as the fire signal, or a slow wail (typically 3x) as to not confuse the public with the standard civil defense signals of alert (steady tone) and attack (fast wavering tone). Fire sirens are often tested once a day at noon and are also called "noon sirens" or "noon whistles".

The first emergency vehicles relied on a bell. Then in the 70s, they switched to a duotone airhorn. Then in the 80s, that was overtaken by an electronic wail.

Piezo Alarm,Siren And Alarm,Piezo Buzzer Siren,Piezo Buzzer Alarm Siren

Jiangsu Huawha Electronices Co.,Ltd , https://www.hnbuzzer.com