任何人都可以帮我写一个触发器。 [英] Can anyone help me writing a trigger.

问题描述

Hello All，



这个SQL比较新，其中一个CodeProjecteer建议我编写一个触发器，以便自动将数据插入TABLE2当数据插入TABLE1时。



我一直在研究很多方法。比如：在asp.net中创建一个我编写SQL命令的按钮，所以当用户点击按钮时，SQL语句就会执行。但主要问题是SQL命令的复杂程度很高。



任何人都可以帮助我创建触发器或任何其他方式来执行我的语句。



谢谢

Saikrishna



我的尝试：



我有以下SQL语句要通过TRIGGER执行



INSERT INTO Table3

SELECT Capacity。[SPOC-Partner]，

COUNT（PMKVY.CentreID）作为候选人，

COUNT（[Aadhar Validation-Aadhar Number Validated] ] ='是'然后1结束）作为验证，

COUNT（[SSC批准结果] ='通过'然后1结束时的情况）作为Results_Pass，

COUNT （当[SSC批准的结果] ='失败'然后1结束时）作为Results_Fail，

COUNT（[SSC批准的结果] ='无值'然后1结束时的情况）作为Results_NA，

SUM（[BankAcountNumber]为空的情况，然后是0，否则1结束）作为银行，

COUNT（案例当[认证] ='是'然后1结束）作为认证，

COUNT（[货币奖励跟踪器 - NSDC处理] ='是'然后1结束时的情况）作为NSDC_Certified，

SUM（[货币奖励追踪器 - 支出清单] ='0'然后0其他1结束时的情况）为DL，

容量。[总目标]

来自PMKVY

INNER JOIN容量PMKVY.CentreID =容量。[培训中心ID]

GROUP BY容量。[SPOC-Partner]，容量。[总目标]

Hello All,

Am relatively new to this SQL and one of the fellow CodeProjecteer suggested me to write a trigger in order to automatically insert a data into the TABLE2 when a data is inserted in TABLE1.

I have been looking into many ways to do it. Like: Creating a button in asp.net where i write a SQL command, so when user clicks on the button the SQL statement executes. But the main issue being the SQL commands are huge in complexities.

Can anyone please help me out to create triggers or any other way to execute my statements.

Thanks
Saikrishna

What I have tried:

I have a following SQL statements to be executed through TRIGGER

INSERT INTO Table3
SELECT Capacity.[SPOC-Partner],
COUNT(PMKVY.CentreID) as Candidate,
COUNT(case when [Aadhar Validation-Aadhar Number Validated] = 'Yes' then 1 end) as Validated,
COUNT(case when [Result Approved by SSC] = 'Pass' then 1 end) as Results_Pass,
COUNT(case when [Result Approved by SSC] = 'Fail' then 1 end) as Results_Fail,
COUNT(case when [Result Approved by SSC] = 'No Value' then 1 end) as Results_NA,
SUM(case when [BankAcountNumber] IS NULL then 0 else 1 end) as Bank,
COUNT(case when [Certified] = 'Yes' then 1 end) as Certified,
COUNT(case when [Monetary Reward Tracker-NSDC Processed] = 'Yes' then 1 end) as NSDC_Certified,
SUM(case when [Monetary Reward Tracker-Disbursement List] = '0' then 0 else 1 end) as DL,
Capacity.[Total Target]
FROM PMKVY
INNER JOIN Capacity ON PMKVY.CentreID=Capacity.[Training Centre ID]
GROUP BY Capacity.[SPOC-Partner],Capacity.[Total Target]

推荐答案

表格本身就有一个触发器，而不是表格上的按钮。



使用SQL命令在表上创建触发器。



您可以从表中查找插入，更新和删除，并编写SQL来执行日志或同步等操作用另一张桌子。



这是MSDN的链接



创建TRIGGER（Transact-SQL） [ ^ ]



页面上的这个例子是一个很好的演示：

A trigger is on the table itself, not a button to wire up on a form.

You create a trigger on the table using SQL commands.

You can look for inserts, updates and deletes from the table and write SQL to do things like log or synchronize with another table.

Here is a link to MSDN

CREATE TRIGGER (Transact-SQL)[^]

This example from the page is a good demonstration:

CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE 
AS
   EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'AdventureWorks2012 Administrator',
        @recipients = '[e-mail]',
        @body = 'Don''t forget to print a report for the sales force.',
        @subject = 'Reminder';
GO

触发器：这是一个数据库在表上执行任何DML（插入，更新，删除）操作的对象。有两种类型的触发器：而不是触发器和After触发器。这里我们将使用after触发器意味着它在服务器完成一个动作时执行。另一个有趣的点是魔术表：插入和已删除。当对表（表2）进行任何DML操作时，它会执行触发器。然后在内部触发器中你想要获取插入的数据，这里插入的表同样对删除（当记录删除时）扮演重要角色。



跟随触发器对象创建表2 。表示当记录将插入 Table2 时，将执行下面的对象。

Trigger: It is a database object which executes any DML(Insert, Update, Delete) operation occurs on a table. There are two types of triggers: Instead of trigger and After trigger. Here we will use after trigger means it executes when server completes an action. One more interesting point is magic tables: inserted and deleted. When any DML operation on a table(Table2) happens it executes a trigger. Then inside trigger you want to fetch what are the data inserted, here inserted table plays vital role likewise for deleted(when record deletes).

Following trigger object created on Table2. Means when a record will insert on Table2 then below object will execute.

-- Create trigger on table Table2 for Insert statement
CREATE TRIGGER trgAfterInsert on Table2
FOR INSERT
AS 

-- Get values from inserted table. Here Table2 is inserted table and you need to change as per your column name.
declare @empid int, @empname varchar(55);
select @empid=i.Column1 from inserted i;
select @empname=i.Column2 from inserted i;

INSERT INTO Table3
SELECT Capacity.[SPOC-Partner], 
COUNT(PMKVY.CentreID) as Candidate,
COUNT(case when [Aadhar Validation-Aadhar Number Validated] = 'Yes' then 1 end) as Validated,
COUNT(case when [Result Approved by SSC] = 'Pass' then 1 end) as Results_Pass,
COUNT(case when [Result Approved by SSC] = 'Fail' then 1 end) as Results_Fail,
COUNT(case when [Result Approved by SSC] = 'No Value' then 1 end) as Results_NA,
SUM(case when [BankAcountNumber] IS NULL then 0 else 1 end) as Bank,
COUNT(case when [Certified] = 'Yes' then 1 end) as Certified,
COUNT(case when [Monetary Reward Tracker-NSDC Processed] = 'Yes' then 1 end) as NSDC_Certified,
SUM(case when [Monetary Reward Tracker-Disbursement List] = '0' then 0 else 1 end) as DL,
Capacity.[Total Target]
FROM PMKVY
INNER JOIN Capacity ON PMKVY.CentreID=Capacity.[Training Centre ID]
GROUP BY Capacity.[SPOC-Partner],Capacity.[Total Target]

建议：如果你没有在触发器内执行Table2的任何数据，那么你可以在存储过程中编写相同的代码。然后在使用 Table2 表执行插入操作时调用它。

Suggestion: If you are not performing any data with Table2 inside trigger then you can write same code inside a stored procedure. Then call it when you are performing insert operation with Table2 table.

这篇关于任何人都可以帮我写一个触发器。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！