Can anyone help me writing a trigger.

Question

3.00/5 (1 vote)

See more:

SQL-Server-2014

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]

Posted 28-Feb-16 4:55am

Member 10376341

Updated 28-Feb-16 7:06am

v2

Add a Solution

2 solutions

Solution 1

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:

SQL

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

Posted 28-Feb-16 6:48am

DotNetSteve

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manas_Kumar · Accepted Answer · 2016-02-28T07:06:00

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.

SQL

-- 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]

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.