How to update table1 from table2 in SQL using cursor and trigger togather

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

I am working in asp.net want to update records from one table to another table. Suppose user have their attendance data in a excel file. I made a browse form from where user will upload excel file . It working very well and storing data from excel to SQL Database. (Table name= Import_Attendance_imp)
Now I want to update another table where i.e TblDailyAttandence
I am using trigger but it updating only one row of, I think there should be use cursor
I have not idea about cursor how to use cursor in my trigger.

please help me

thank in advance

What I have tried:

ALTER TRIGGER [dbo].[TRIG_Update_Attendance_imp]
ON [dbo].[Import_Attendance_imp]
AFTER INSERT

AS

BEGIN
DECLARE
@EmpCode VARCHAR(100),
@Monthyear VARCHAR(100),
@AtCol VARCHAR(100),
@Status VARCHAR(100),
@in VARCHAR(100),
@inTime VARCHAR(100),
@out VARCHAR(100),
@outTime VARCHAR(100)

SELECT @AtCol = Att,@in=[in],@inTime=inTime,@out=[out],@outTime=outTime, @EmpCode = empcode, @Status = [status],@Monthyear=Monthyear FROM INSERTED
DECLARE @query VARCHAR(MAX) = ''

SET @query = 'UPDATE TblDailyAttandence SET '+@AtCol+' = '''+@Status+''','+@in+' = '''+@intime+''','+@out+' = '''+@outtime+'''
WHERE EmpCode = '''+@EmpCode+''' AND Monthyear='''+@Monthyear+''''

PRINT @query

EXECUTE(@query)

END

Posted 9-Jun-17 1:25am

arvindcis

Updated 9-Jun-17 23:00pm

Add a Solution

Comments

ZurdoDev 9-Jun-17 8:18am

Triggers run on sets of data so you don't ever want to use variables like this because it will only work on one record and will mess up all other records in the inserted or deleted tables.

You don't need a trigger. In your update procedure just update the attendance table as well.

Richard Deeming 9-Jun-17 11:49am

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

1 solution

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)

NightWizzard · Accepted Answer · 2017-06-09T23:00:00

A cursor would slow down performance and in your case the count of updated records is only reduced to one because you're transporting the values via variables which reduces the read operation on the temporary INSERTED table to the first record. To resolve your problem simply join and assign the values needed in the UPDATE statement directly from INSERTED and it will work. Do something like this:

SQL

UPDATE a
   SET a.[Field1] = b.[Field1]
     , a.[Field2] = b.[Field2]
     , ...
  FROM [dbo].[TblDailyAttandence] a
   INNER JOIN inserted b ON b.[Empcode] = a.[empcode] AND b.[Monthyear] = a.[MonthYear]