One place for hosting & domains

      Working

      Working with Environment Variables in Vue.js


      While this tutorial has content that we believe is of great benefit to our community, we have not yet tested or
      edited it to ensure you have an error-free learning experience. It’s on our list, and we’re working on it!
      You can help us out by using the “report an issue” button at the bottom of the tutorial.

      In this post, we’ll learn how to work with distinct configurations between development and production mode for Vue.js projects that use the CLI’s webpack template.

      In a web app, we most likely have to access a backend API server through a URL. This URL can be something like http://localhost:8080/api while in development, and https://site.com/api in production when the project is deployed. Environment variables allow us for an easy way to change this URL automatically, according to the current state of the project.

      An easy way to use environment variables with Vue and the webpack template is through files with a .env extension. These files become responsible for storing information that’s specific to the environment (development, testing, production,…)

      The majority of this post applies to apps using v2.x of the Vue CLI, but environment variables are just as easy to manage in the Vue CLI v3.

      Using .env Files in Vue

      The simplest way to use .env files in Vue is to create an application that already supports environment files. Let’s use the vue-cli and the webpack template for that.

      With Node 8 or higher installed, run the following, where my-app is your app name:

      $ npx vue-cli init webpack my-app
      

      This command will create an application with several files ready for use. In this post, we’re focusing only on the environment configuration, which can be accessed in the config directory:

      Project file structure

      There are two files in the config directory: dev.env.js and prod.env.js, and you’ll also have a test.env.js file if you’ve configured tests while initiating the project. These files are used in development and production mode, or in other words, when you are running the application through the npm run dev command, the dev.env.js file is used, and when you compile the project for production with the npm run build command, the prod.env.js file is used instead.

      Let’s change the development file to:

      dev.env.js

      'use strict'
      const merge = require('webpack-merge')
      const prodEnv = require('./prod.env')
      
      module.exports = merge(prodEnv, {
        NODE_ENV: '"development"',
        ROOT_API: '"http://localhost/api"'
      })
      

      Our development environment file has an additional variable called ROOT_API, with the value http://localhost/api.

      Now let’s change the production file to:

      prod.env.js

      'use strict'
      module.exports = {
        NODE_ENV: '"production"',
        ROOT_API: '"http://www.site.com/api"'
      }
      

      Here we have the same ROOT_API variable, but with a different value, which should only be used in production mode. Note how string variables need the double quotes inside the single quotes.

      Using the Environment Files in Your Code

      After creating the ROOT_API variable, we can use it anywhere in Vue through the global process.env object:

      process.env.ROOT_API
      

      For example, open the src/components/HelloWorld.vue file and in the <script> tag add the following:

      mounted() {
        console.log(process.env.ROOT_API)
      }
      

      After running npm run dev, you will see the console.log information in the browser dev tools:

      Running the app

      If you run the npm run build command, the dist directory will be created with the application ready to be deployed to a production environment, and the variable ROOT_API will display the value http://www.site.com./api, as specified in prod.env.js.

      Thus, we can work with different variables for each different environment, using the ready-made configuration that the webpack template provides us. If you use another template, make sure you find an equivalent feature or use a library like dotenv to manage your environment variables.

      What About Vue CLI 3?

      If your app is using the new Vue CLI, you’ll want to instead have files like .env and .env.prod at the root of your project and include variables like this:

      .env

      VUE_APP_ROOT_API=http://localhost/api
      

      .env.prod

      VUE_APP_ROOT_API=http://www.site.com/api
      

      The VUE_APP_ prefix is important here, and variables without that prefix won’t be available in your app.



      Source link

      10 Ways You Can Create ‘Watercooler Moments’ While Working Remotely


      Whether it’s an actual watercooler, the kitchen’s hardworking Keurig machine, or the breakroom foosball table, these workplace retreats give employees a place to congregate, shoot the breeze, and engage in non-work-related chatter.

      While animated discussions about Game of Thrones or Netflix might seem antithetical to rise-and-grind culture, in reality, “watercooler talk” is crucial to the success of businesses.

      Far from being detrimental to productivity, studies show that casual workplace conversation and no-pressure socializing yields a multitude of benefits, including increased productivity and collaboration, the establishment of trust between employees, and strengthened team building and engagement. Not only do the mental and physical breaks help boost cognitive function, but they ultimately lead to a stronger, more united, and well-connected team that produces better work and better ideas. It’s true!

      Never has the power of watercooler talk been more necessary than now. As business teams adapt to remote-work environments in the wake of global health concerns, enjoying the benefits of casual workplace conversation (even in a virtual setting) should be a high priority. But how do we build rapport in an entirely digital world?

      Let’s discuss (over a mid-day coffee break, perhaps?).

      Working From Home?

      Now is the perfect time to build a website. We offer budget-friendly Shared Hosting services with robust features to help you thrive online. Plans start at $2.59/mo.

      10 Ways You Can Manufacture Watercooler Moments Remotely

      As you’ve swapped ties and slacks for your comfy sweats and house slippers, your usual 9-to-5 schedule is probably looking a little different these days. Outfitted at your makeshift desk with your favorite at-home coffee mug (and now an ever-present bottle of hand sanitizer), you’re bringing the office home — for the foreseeable future.

      We’d venture to guess a couple things as you’re adapting to this new normal.

      1. You’ve probably already blown through your quarantine snacks. Trust us, you’re not the only one.
      2. You’re probably getting some pretty bad cabin fever and feeling the social isolation as you work from home while complying with unprecedented social-distancing guidelines.

      Since you are not engaging IRL with your team on a daily basis or running into each other in the lunchroom, it can become really easy to simply do your work, disregarding the necessary interaction and team-building of the workplace.

      So how can your newly-remote team keep morale high and strengthen engagement while working separately in your own individual homes? As a leader, you can be intentional about making these important moments happen. Let us share a few ways how.

      1. Play Games

      Get the ball rolling by infusing team meetings and communications with ice breakers, trivia, or other fun, casual games. Whether you’re using Slack or another team collaboration tool, use it to interject fun asides into the workday that break up the monotony and bring humor and fun.

      2. Make Time for Small Talk

      Conversations with coworkers don’t have to be all work and no play. Leaders should intentionally create the time and space for de-stressing with non-work-related banter. By initiating casual discussion, you not only get to know your colleagues better, but you provide needed physical and mental breaks. Actively encouraging and participating in these kinds of conversations help build trust, unity, and inclusion.

      • Try introducing team members virtually. Conduct spotlights or round-robin, get-to-know-you questions.
      • Highlight birthdays with rousing video chat versions of “happy birthday” (after all, you should be getting lots of singing practice with all that hand washing you’re doing, right?).
      • Chronicle your weekends: What at-home activities did you do? What was your last great Netflix binge? What funny thing did your kids say? Thoughts on that viral video making the rounds on social media?

      Start the convo and embrace the beneficial small talk. Make it a necessary part of each day, and your culture as a whole — creativity and innovation increases when people feel like they can interact freely.

      3. Using Google Hangouts to Swap Skills

      You might be surprised at what talents your coworkers have hidden under their belts. Use video calls to host employee-led classes, letting team members teach others a skill, like baking bread, harvesting honey, or doing watercolor paintings.

      There’s a lot of cool technology out there that can connect you with people everywhere and grant you essential face time with your team — it’s time to take advantage of it! Also, it’s just nice to see facial expressions and body language after quarantining all alone with your screen.

      Plus, you’ll be having fun and learning more about your employees while helping foster shared experiences and socialization that engenders a positive work-life balance. What’s more, understanding (and keeping notes on) the not-so-visible-at-work talents of your team members can help you utilize their skills to better your business in the future. These are major wins.

      4. Host a Virtual Team Book Club

      Less time commuting to and from the office means more time for making a dent in your To-Be-Read list. Join together with coworkers to host a virtual book club, swapping ideas and thoughts on non-work-related (but still significant) topics. Who knows, you might keep it up in-person when you’re all back at the office!

      5. Create (Good) Competition

      Whether you’re voting on who has the cutest pet or who wins the Washing-Your-Hands Dance Challenge, give your team a little boost by fostering work and non-work-related competition that keeps things exciting.

      Try swapping typing-test WPM scores, making funny memes, playing charades with emojis only, battling over reality-tv-show-contestant brackets, sharing your best wastebasket toss, creating and voting on the best lip sync video, or battling it out on brain teasers.

      6. Openly Give Kudos

      Establish a virtual space on your team communication channels where team members can display and applaud the good work of other employees. Fostering praise helps team members feel appreciated and motivated; plus, this fun socialization practice helps your team remain unified — no matter the physical distance that separates you.

      7. Team Up Like-Minded People

      Are there fitness-minded employees on your team? What about cat lovers? Bibliophiles?

      Create opportunities for team members to branch off and chat with others about shared interests. A dedicated channel on Slack can allow health gurus to swap recipes or workout tips, foodies to talk must-visit restaurants, or feline fanatics to share the best cat videos. If appropriate, help team members connect with each other on their own social channels.

      Getting team members to connect personally and engage with others in dedicated spaces (especially about things they’re interested in) increases feelings of togetherness and engagement. Feel the bond.

      8. Encourage Healthy Habits

      The transition to less formal, remote work might mean some employees aren’t taking their lunch-break walks or sticking to a regular sleep schedule. Take time in meetings to emphasize the importance of reducing stress and caring for yourself, physically and mentally.

      That might mean doing short meditations together, stopping brainstorming sessions to stretch together, or mandating a water break. Demonstrating that you care for the well-being of your team builds trust.

      Plus, prioritizing health will benefit your business as a whole. Happy workers = successful business.

      Want More Leadership Content?

      Subscribe to our monthly newsletter so you never miss an article.

      9. Host Themed Events.

      Every week, create themes on your communication channels to encourage fun interactions. Swap funny photos on a Wacky Hair themed workday, or compare best April Fools’ jokes on the 1st. These kinds of virtual group events can allow conversations to be more personal and fun.

      10. Get in the Groove.

      Most employees already have a habit of tuning into music at work. Join in (and encourage a positive work-life quality) by offering a company-wide subscription to a music streaming service like Spotify or Apple Music.

      Swap playlists with your team, vote on the best Justin (Bieber vs. Timberlake), and share favorite tunes. Music taste is another excellent way to get to know your team!

      Social distancing doesn’t have to mean social isolation, especially in the workplace. Forget working in a vacuum — small talk equals big benefits, even virtually. Keep the morale and productivity of your team high as you intentionally foster one-of-a-kind watercooler moments from your at-home workspace.

      And hey, now there’s no need to refill those painfully-heavy office water jugs! Bonus?



      Source link

      Working with Triggers in a MySQL Database – A Tutorial


      Updated by Linode Contributed by Francis Ndungu

      A trigger is a pre-defined SQL command that is automatically executed when specific actions occur in the database. It can be fired either before or after an INSERT, UPDATE, or DELETE event.

      Triggers are mainly used to maintain software logic in the MySQL server, and they have several benefits:

      • Triggers help keep global operations centralized in one location.

      • They reduce client-side code and help minimize the round-trips made to the database server.

      • They help make applications more scalable across different platforms.

      Some common use-cases of triggers include audit logging, pre-computing database values (e.g. cumulative sums), and enforcing complex data integrity and validation rules.

      In this guide, you will learn:

      Before You Begin

      Make sure you have the following:

      1. A configured Linode server. You can learn how to create and setup a Linode server by reading our Getting Started with Linode guide.

      2. A MySQL server and client installed on the Linode server. Installation guides for MySQL are available for different distributions in our MySQL section.

      Prepare the Database

      To better understand how triggers work, we will create a sample database and add sample data into it. Later, we will create different triggers on the database as a proof of concept exercise.

      1. First, log in to your MySQL Server:

        mysql -u root -p
        

        Then, enter the root password of your MySQL server and hit Enter to proceed.

      2. Next, you will see a MySQL prompt similar to the one shown below:

          
        mysql >
        
        
      3. Create a test_database by running the command below:

        CREATE DATABASE test_database;
        

        Output:

          
        Query OK, 1 row affected (0.02 sec)
        
        
      4. Switch to the database:

        USE test_database;
        

        Output:

          
        Database changed
        
        
      5. Once the database is selected, we will create some tables that we will use for demonstrating triggers. We will begin by creating the stores table. This table will hold information about two sample stores/offices where our hypothetical business operates from:

        CREATE TABLE stores
        (
        store_id BIGINT PRIMARY KEY AUTO_INCREMENT,
        store_name VARCHAR(50)
        ) ENGINE=InnoDB;
        

        Output:

          
        Query OK, 0 rows affected (0.07 sec)
        
        
      6. Next, add two records to the stores table by running the commands below:

        INSERT INTO stores (store_name) VALUES ('Philadelphia');
        INSERT INTO stores (store_name) VALUES ('Galloway');
        

        After each command, you will get the below output:

          
        Query OK, 1 row affected (0.08 sec)
        ...
        
        
      7. Confirm the records by running the command below:

        SELECT * FROM stores;
        

        Output:

          
        +----------+--------------+
        | store_id | store_name   |
        +----------+--------------+
        |        1 | Philadelphia |
        |        2 | Galloway     |
        +----------+--------------+
        2 rows in set (0.01 sec)
        
        
      8. Next, create the products table. The table will hold different products being offered in the store:

        CREATE TABLE products
        (
        product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
        product_name VARCHAR(40),
        cost_price DOUBLE,
        retail_price DOUBLE,
        availability VARCHAR(5)
        ) ENGINE=InnoDB;
        

        Output:

          
        Query OK, 0 rows affected (0.13 sec)
        
        
        • Each product will be uniquely identified by a product_id.

        • A product_name field will specify the names of the items.

        • The cost_price and retail_price fields will determine the buying and selling price respectively.

        • An availability column will define the product availability in the different stores. If the product is only available in our local store (Philadelphia), we will denote it with a LOCAL value. Else, we will use the value of ALL to signify a product that is available in both stores (Philadelphia and Galloway).

      9. Add sample data to the products table:

        INSERT INTO products (product_name, cost_price, retail_price, availability) VALUES ('WIRELESS MOUSE', '18.23', '30.25','ALL');
        
        INSERT INTO products (product_name, cost_price, retail_price, availability) VALUES ('8 MP CAMERA', '60.40', '85.40','ALL');
        
        INSERT INTO products (product_name, cost_price, retail_price, availability) VALUES ('SMART WATCH', '189.60', '225.30','LOCAL');
        

        You will get the output shown below after each insert command:

          
        Query OK, 1 row affected (0.02 sec)
        ...
        
        
      10. Confirm if the products were inserted by running the command below:

        SELECT * FROM products;
        

        Output:

          
        +------------+----------------+------------+--------------+--------------+
        | product_id | product_name   | cost_price | retail_price | availability |
        +------------+----------------+------------+--------------+--------------+
        |          1 | WIRELESS MOUSE |      18.23 |        30.25 | ALL          |
        |          2 | 8 MP CAMERA    |       60.4 |         85.4 | ALL          |
        |          3 | SMART WATCH    |      189.6 |        225.3 | LOCAL        |
        +------------+----------------+------------+--------------+--------------+
        3 rows in set (0.00 sec)
        
        
      11. Next, the products’ availability will be mapped to another table named products_to_stores. This table will just reference the product_id from the products table and the store_id from the stores table where the item is available.

        Create the products_to_stores table by running the code below:

        CREATE TABLE products_to_stores
        (
        ref_id BIGINT PRIMARY KEY AUTO_INCREMENT,
        product_id BIGINT,
        store_id BIGINT
        ) ENGINE=InnoDB;
        

        Output:

          
        Query OK, 0 rows affected (0.14 sec)
        
        
      12. Next, we will create an archived_products table. The table will hold information about deleted products for future reference:

        CREATE TABLE archived_products
        (
        product_id BIGINT PRIMARY KEY ,
        product_name VARCHAR(40),
        cost_price DOUBLE,
        retail_price DOUBLE,
        availability VARCHAR(5)
        ) ENGINE=InnoDB;
        

        Output:

          
        Query OK, 0 rows affected (0.14 sec)
        
        
      13. Lastly, we will create a products_price_history table for tracking the different prices of each product over time:

        CREATE TABLE products_price_history
        (
        product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
        price_date DATETIME,
        retail_price DOUBLE
        ) ENGINE=InnoDB;
        

        Output:

          
        Query OK, 0 rows affected (0.14 sec)
        
        

      Once our database structure is in place, we can now go ahead and learn the basic syntax of a MySQL database trigger in order to create our first sample.

      Trigger Syntax

      As indicated earlier, triggers are fired automatically either before or after an SQL command is run in the database. The basic syntax for creating triggers is as follows:

      CREATE TRIGGER TRIGGER_NAME
      
      TRIGGER_TIME TRIGGER_EVENT
      
      ON TABLE_NAME FOR EACH ROW
      
      [TRIGGER BODY];
      
      • TRIGGER_NAME: Each trigger must have a unique name and you should define it here.

      • TRIGGER_TIME: Either BEFORE or AFTER.

      • TRIGGER_EVENT: You need to specify the database event that will invoke the trigger: INSERT, UPDATE, or DELETE.

      • TRIGGER BODY: This specifies the actual SQL command (or commands) that you want to be run by your trigger.

      If a trigger body has more than one SQL statement, you must enclose it within a BEGIN...END block. As well, you will need to temporarily change the DELIMITER that signals the end of the trigger body to a new value. This ensures that the statements within the body are not prematurely interpreted by your MySQL client. An example of this looks like the following:

      DELIMITER &&
      
      CREATE TRIGGER TRIGGER_NAME
      
      TRIGGER_TIME TRIGGER_EVENT
      
      ON TABLE_NAME FOR EACH ROW
      
      BEGIN
      
      [TRIGGER BODY]
      
      END &&
      
      DELIMITER ;
      

      Note

      The last line of this example changes the DELIMITER back to the default ; value.

      Creating Before Event Triggers

      In this section, we will look into the different types of triggers that are fired before a database operation. These include the BEFORE INSERT, BEFORE UPDATE, and BEFORE DELETE triggers.

      Creating a Before Insert Trigger

      We will create our first BEFORE INSERT trigger. The trigger will make sure that the retail price of a product is greater than the cost price whenever items are inserted into the products table. Otherwise, the database user will get an error.

      1. While still on the mysql > prompt, enter the command below:

        DELIMITER $$
        
        CREATE TRIGGER price_validator
        
        BEFORE INSERT
        
        ON products FOR EACH ROW
        
        IF NEW.cost_price>=NEW.retail_price
        
        THEN
        
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Retail price must be greater than cost price.';
        
        END IF $$
        
        DELIMITER ;
        
        • The above code defines the trigger name (price_validator), time (BEFORE), event (INSERT), and the table (products) to be affected.

        • Our trigger uses the NEW keyword to check the cost_price and retail_price before a record is inserted to the products table, using the IF...THEN...END IF statement.

        • If the cost_price is greater or equal to the retail price, our triggers tells MySQL to throw a custom exception instructing the user to rectify the error.

      2. To test the trigger above, try inserting a product that violates the validation rule:

        INSERT INTO products (product_name, cost_price, retail_price, availability) VALUES ('GAMING MOUSE PAD', '145.00', '144.00','LOCAL');
        

        Output:

          
        ERROR 1644 (45000): Retail price must be greater than cost price.
        
        

        The above insert commands should fail because the retail_price (144.00) is not greater than the cost_price (145.00).

      Creating a Before Update Trigger

      Next, we will create a BEFORE UPDATE trigger. This trigger will prevent database users from editing a product name once a product has been inserted into the database. If you have multiple users working in the database, a BEFORE UPDATE trigger may be used to make values read-only, and this can prevent malicious or careless users from modifying records unnecessarily.

      1. Create a new product_name_validator trigger with the command below:

        DELIMITER $$
        
        CREATE TRIGGER product_name_validator
        
        BEFORE UPDATE
        
        ON products FOR EACH ROW
        
        IF NEW.product_name<>OLD.product_name
        
        THEN
        
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Product name is read-only and it can not be changed.';
        
        END IF $$
        
        DELIMITER ;
        

        This trigger compares the values of the new product_name (NEW.product_name) and the old name already in the database (OLD.product_name). If there is a mismatch, an exception is thrown.

      2. To invoke the product_name_validator trigger, we can attempt to update the name of the product with the ID 1:

        UPDATE products SET product_name='WIRELESS BLUETOOTH MOUSE' WHERE product_id='1';
        

        Output:

          
        ERROR 1644 (45000): Product name is read-only and it can not be changed.
        
        

      Defining a Before Delete Trigger

      In this section, you will see how you can define a BEFORE DELETE trigger to prevent users from deleting specific records from a table.

      1. To create the prevent_delete trigger, run the command below:

        DELIMITER $$
        
        CREATE TRIGGER prevent_delete
        
        BEFORE DELETE
        
        ON products FOR EACH ROW
        
        IF OLD.availability='ALL'
        
        THEN
        
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'The product can not be deleted because it is available in ALL stores.';
        
        END IF $$
        
        DELIMITER ;
        

        This trigger will prevent products marked with a value of ALL in the availability column from being deleted.

      2. Next, try to delete the first product from the products table and see if the trigger will be invoked:

        DELETE FROM products WHERE product_id='1';
        

        Output:

          
        ERROR 1644 (45000): The product can not be deleted because it is available in ALL stores.
        
        

      We have looked at the different triggers that are invoked before a database operation. Next, we will look into the other types of triggers that are fired after database events.

      Creating After Event Triggers

      In a production environment, you may want some triggers to be automatically executed after a database event occurs (for example, inserting records into different tables). The examples below demonstrate how these kinds of triggers can be used in our sample database.

      Creating an After Insert Trigger

      This example creates a trigger named product_availability that inserts mapping records into the products_to_stores table. This trigger is used to enforce business logic; in particular, it helps define the product availability for the different stores.

      1. Run the code below to create the product_availability trigger. Since we have multiple lines of code in the trigger body, we will use a BEGIN...END block:

        DELIMITER $$
        
        CREATE TRIGGER product_availability
        
        AFTER INSERT
        
        ON products FOR EACH ROW
        
        BEGIN
        
        IF NEW.availability='LOCAL' then
        
        INSERT INTO products_to_stores (product_id, store_id) VALUES (NEW.product_id, '1');
        
        ELSE
        
        INSERT INTO products_to_stores (product_id, store_id) VALUES (NEW.product_id, '1');
        
        INSERT INTO products_to_stores (product_id, store_id) VALUES (NEW.product_id, '2');
        
        END IF;
        
        END $$
        
        DELIMITER ;
        
        • When an item is being inserted into the products table, the trigger will check the availability field.

        • If it is marked with the LOCAL value, the product will be made available in one store only.

        • Any other value will instruct the trigger to make the product available to the two stores that we created earlier.

      2. To see the product_availability trigger in action, insert the two records to the products table:

        INSERT INTO products (product_name, cost_price, retail_price, availability) VALUES ('BLUETOOTH KEYBOARD', '17.60', '23.30','LOCAL');
        INSERT INTO products (product_name, cost_price, retail_price, availability) VALUES ('DVB-T2 RECEIVE', '49.80', '53.40','ALL');
        
      3. Then, query the products_to_stores table:

        SELECT * FROM products_to_stores;
        

        You should see an output similar to the one shown below:

          
        +--------+------------+----------+
        | ref_id | product_id | store_id |
        +--------+------------+----------+
        |      1 |          4 |        1 |
        |      2 |          5 |        1 |
        |      3 |          5 |        2 |
        +--------+------------+----------+
        3 rows in set (0.00 sec)
        
        

      Defining an After Update Trigger

      A trigger can also be fired after an UPDATE event. We will see how we can leverage this type of trigger to keep track of price changes in our store over time.

      1. Create a product_history_updater trigger by running the command below:

        CREATE TRIGGER product_history_updater
        
        AFTER UPDATE
        
        ON products FOR EACH ROW
        
        INSERT INTO products_price_history (product_id, price_date, retail_price) VALUES (OLD.product_id, NOW(), NEW.retail_price);
        

        This trigger records changes to a product’s retail_price in the products_price_history table.

        Note

        Unlike previous examples, this trigger only has one statement in the trigger’s body, so we do not need to change the DELIMITER.

      2. Then, try updating the price of the first product by running the command below:

        UPDATE products SET retail_price='36.75' WHERE product_id='1';
        
      3. Next, query the products_price_history table to see if the price change was logged:

        SELECT * FROM products_price_history;
        

        If the trigger worked as expected, you should get the below output:

          
        +------------+---------------------+--------------+
        | product_id | price_date          | retail_price |
        +------------+---------------------+--------------+
        |          1 | 2020-01-28 11:46:21 |        36.75 |
        +------------+---------------------+--------------+
        1 row in set (0.00 sec)
        
        

      Creating an After Delete Trigger

      In some cases, you might want to log delete operations after a specific action has occurred in the database. You can achieve this by using the AFTER DELETE trigger.

      1. Create a new the product_archiver trigger with the command below:

        CREATE TRIGGER product_archiver
        
        AFTER DELETE
        
        ON products FOR EACH ROW
        
        INSERT INTO archived_products (product_id, product_name, cost_price, retail_price, availability) VALUES (OLD.product_id, OLD.product_name, OLD.cost_price, OLD.retail_price, OLD.availability);
        

        This trigger archives deleted products in a separate table named archived_products. When an item is deleted from the main products table, our trigger will automatically log it to the archived_products table for future reference.

      2. Next, delete a product from the products table and see if the trigger will be invoked:

        DELETE FROM products WHERE product_id='3';
        
      3. Now, if you check the archived_products table, you should see one record:

        SELECT * FROM archived_products;
        

        Output:

          
        +------------+--------------+------------+--------------+--------------+
        | product_id | product_name | cost_price | retail_price | availability |
        +------------+--------------+------------+--------------+--------------+
        |          3 | SMART WATCH  |      189.6 |        225.3 | LOCAL        |
        +------------+--------------+------------+--------------+--------------+
        1 row in set (0.00 sec)
        
        

      Deleting a Trigger

      You have seen the different types of triggers and how they can be used in a production environment. Sometimes, you may want to remove a trigger from the database.

      You can delete a trigger if you don’t want to use it anymore using the syntax below:

      DROP TRIGGER IF EXISTS TRIGGER_NAME;
      

      Note

      The IF EXISTS keyword is an optional parameters that only deletes a trigger if it exists.

      For example, to delete the product_archiving trigger that we defined above, use the below command:

      DROP TRIGGER IF EXISTS product_archiver;
      

      Output:

        
      Query OK, 0 rows affected (0.00 sec)
      
      

      Caution

      Be cautious when deleting tables associated with triggers. Once a table is dropped from the MySQL database, the related triggers are also automatically deleted.

      More Information

      You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.

      This guide is published under a CC BY-ND 4.0 license.





      Source link