USE Master GO If exists (select name from sysobjects where xtype = 'u' and name = 'cigarette inventory table ') Drop table cigarette inventory TABLE GO If exists (select name from sysobjects where xtype = 'u' and name = 'cigarette sale table ') Drop table cigarette sales TABLE GO -- Business rule: sales amount = sales quantity * Sales unit price business rule. Create table cigarette sales TABLE ( Cigarette brand VARCHAR (40) primary key not null, Purchaser VARCHAR (40) NULL, Sales quantity int null, Unit Price: money null, Sales amount: MONEY NULL ) GO -- Business rules: inventory amount = inventory quantity * inventory unit price business rules. Create table cigarette inventory TABLE ( Cigarette brand VARCHAR (40) primary key not null, Inventory quantity int null, Inventory Unit Price: money null, Inventory amount: MONEY NULL ) GO -- Create a trigger, Example 1 /* Create a trigger [T_INSERT _ cigarette inventory table], which is relatively simple. Note: This trigger is triggered whenever an INSERT action occurs in the [cigarette inventory table. Trigger function: enforce business rules to ensure that the inserted data is in stock amount = inventory quantity * inventory unit price. Note: [INSERTED] and [DELETED] are system tables. They cannot be created, modified, or DELETED, but can be called. Important: The two system tables have the same structure as the table that inserts data. */ If exists (select name from sysobjects where xtype = 'tr' and name = 't_ INSERT _ cigarette inventory table ') Drop trigger T_INSERT _ cigarette inventory table GO Create trigger T_INSERT _ cigarette inventory table ON cigarette inventory table FOR INSERT AS -- Commit transaction processing BEGIN TRANSACTION -- Execute the following statements to ensure business rules UPDATE cigarette inventory table SET inventory amount = inventory quantity * inventory unit price WHERE cigarette brand IN (SELECT cigarette brand from INSERTED) COMMIT TRANSACTION GO /* Insert test data for the [cigarette inventory table: Note: the data in the first data (the new power of Hongtashan) conforms to the business rules, [Inventory amount] is blank in the second data (Hongtashan Manfeng) and does not comply with business rules, Article 3 in the data (Yunnan image), [inventory amount] is not equal to [inventory quantity] multiplied by [inventory unit price] and does not comply with business rules. Article 4 The Data inventory quantity is 0. After inserting data, check whether the data in the [cigarette inventory table] is stock amount = stock quantity * stock unit price. */ Insert into cigarette inventory table (cigarette brand, inventory quantity, inventory unit price, inventory amount) SELECT 'hongtashan neoligan', 1200, UNION ALL SELECT 'hongta mountain others', NULL UNION ALL SELECT 'Yunnan image', 500, UNION ALL SELECT 'yuxi ', 0, 30, 0 GO -- Query data SELECT * FROM cigarette inventory table GO /* Result set RecordId cigarette brand inventory quantity stock unit price stock amount -------------------------------------------- 1 Hongtashan new forces 100 12.0000 1200.0000 2 Hongta mountain 100 22.0000 2200.0000 3. Yunnan Image 100 60.0000 6000.0000 4 Yuxi 0 30.0000. 0000 (The number of affected rows is 4) */ -- Trigger Example 2 /* Create a trigger [T_INSERT _ cigarette sales table], which is complex. Note: This trigger is triggered whenever an INSERT action occurs in the [cigarette inventory table. Trigger function: implements business rules. Business rule: if the brand of cigarettes sold does not exist or the inventory is zero, an error is returned. Otherwise, the inventory quantity and amount of cigarettes of the corresponding brand in the [cigarette inventory table] will be automatically reduced. */ If exists (select name from sysobjects where xtype = 'tr' and name = 't_ INSERT _ ') Drop trigger T_INSERT _ cigarette sales table GO Create trigger T_INSERT _ cigarette sales table ON cigarette sales table FOR INSERT AS BEGIN TRANSACTION -- Check the validity of the data: whether the cigarettes sold have inventory or whether the inventory is greater than zero If not exists ( SELECT inventory quantity FROM cigarette inventory table WHERE cigarette brand IN (SELECT cigarette brand from inserted) ) BEGIN -- Return error message RAISERROR ('error! The cigarette does not exist in stock and cannot be sold. ', 16,1) -- Roll back the transaction ROLLBACK RETURN END If exists ( SELECT inventory quantity FROM cigarette inventory table WHERE cigarette brand IN (SELECT cigarette brand from inserted) AND Inventory quantity <= 0 ) BEGIN -- Return error message RAISERROR ('error! The cigarette inventory is smaller than or equal to 0 and cannot be sold. ', 16,1) -- Roll back the transaction ROLLBACK RETURN END -- Process valid data -- Execute the following statements to ensure business rules UPDATE cigarette sales table SET sales amount = sales quantity * Sales unit price WHERE cigarette brand IN (SELECT cigarette brand from inserted) DECLARE @ cigarette brand VARCHAR (40) SET @ cigarette brand = (SELECT cigarette brand from inserted) DECLARE @ sales volume MONEY SET @ sales volume = (SELECT sales volume from inserted) UPDATE cigarette inventory table SET inventory quantity = inventory quantity-@ sales quantity, Inventory amount = (inventory quantity-@ sales quantity) * inventory unit price WHERE cigarette brand = @ cigarette brand COMMIT TRANSACTION GO -- Keep track of the data changes in the [cigarette inventory table] and [cigarette sales table. -- For the [cigarette sales table], insert the first test data, which is normal. Insert into cigarette sales table (cigarette brand, purchaser, sales quantity, sales unit price, sales amount) SELECT 'hongtashan neolive', 'a commodity quota', 1200 GO -- For the [cigarette sales table], insert the second test data. The sales amount of this data is not equal to the sales unit price * sales quantity. -- The trigger will automatically correct the data so that the sales amount is equal to the sales unit price * sales quantity. Insert into cigarette sales table (cigarette brand, purchaser, sales quantity, sales unit price, sales amount) SELECT 'hongta mountain others', 'a buyer ', 2000 GO -- For the [cigarette sales table], insert the third test data. The cigarette brands in the data cannot be found in the cigarette inventory table. -- Trigger will report an error. Insert into cigarette sales table (cigarette brand, purchaser, sales quantity, sales unit price, sales amount) SELECT 'honghe V8', 'buyer ', 10, 60, 600 GO /* Result set Server: message 50000, level 16, status 1, process T_INSERT _ cigarette sales table, row 15 Error! The cigarette does not exist in stock and cannot be sold. */ -- For the [cigarette sales table], insert the third test data, in which the inventory of cigarette brands is 0 in the cigarette inventory table. -- Trigger will report an error. Insert into cigarette sales table (cigarette brand, purchaser, sales quantity, sales unit price, sales amount) SELECT 'yuxi ', 'a buyer', 10, 30, 300 GO /* Result set Server: message 50000, level 16, status 1, process T_INSERT _ cigarette sales table, row 29 Error! The cigarette inventory is smaller than or equal to 0 and cannot be sold. */ -- Query data SELECT * FROM cigarette inventory table SELECT * FROM cigarette sales table GO /* |