Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

update trigger in sql server

Posted on 2013-10-28
7
593 Views
Last Modified: 2013-10-29
hi,
i tried to understand how triggers work, but i guess it will take time. i have a situation where user update a column(pinCode) in auth. table. after updating only that column, i want trigger runs and update the nursePin columns values in nurse & rn tables.
auth.jpg
nurseTable.jpg
rnTable.jpg
0
Comment
Question by:s_hausen
7 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39607776
Please share the table structure also if you want exact queries
as I am not getting the key fields from this .there must be some ID (primary key)  to relate these table on the babis of this table we need to update the fields .

Try someting like thus

create TRIGGER [dbo].[tr_Pin_Modified]
   ON [dbo].[Auth]
   AFTER UPDATE
AS BEGIN
    SET NOCOUNT ON;
    IF UPDATE (pinCode)
    begin

        UPDATE N
        SET N.nursePin = I.pinCode
           
        FROM NurseTable N INNER JOIN Inserted I
            ON N.ID = I.ID
 UPDATE R
        SET R.nursePin = I.pinCode
           
        FROM RnTable R INNER JOIN Inserted I
            ON N.ID = I.ID
       
    end
END
0
 
LVL 5

Expert Comment

by:MohitPandit
ID: 39607817
Hello,

I would like to add more things.

1. We can have trigger on Table, View, Server, Database
2. I will continue with Table, View trigger --> we have further three types of trigger i.e. "FOR", "AFTER", "INSTEAD OF"
3. We have "Magic Tables in trigger i.e. "Inserted" and "Deleted". In "Inserted", engine stores new value and in "Deleted", engine stores old values.

Please find below links for detail study.

http://technet.microsoft.com/en-us/library/ms189799%28v=sql.100%29.aspx
http://blog.sqlauthority.com/category/sql-trigger/

Kindly take a look over it and let us know in case you have any concern.

Best Regards,
Mohit Pandit
0
 

Author Comment

by:s_hausen
ID: 39608765
hi,
i forgot to mention that there is no relationship between tables, the only thing relate to them is the pincode. so if pincode changes in auth. table, trigger need to find and replace the old pincodes to newpincodes in rn and nurse tables. so if there's a pin: 1234 and user change it to 5678 then, trigger need to change the values of 1234 in nurse and rn tables to 5678.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39609207
CREATE TRIGGER dbo.Auth_trg_pinCode_Change
ON dbo.auth
AFTER UPDATE
AS
SET NOCOUNT ON
IF UPDATE(pinCode)
BEGIN
    IF (SELECT COUNT(*) FROM (SELECT TOP (2) pinCode FROM inserted) AS derived) > 1
    BEGIN
        RAISERROR('Cannot update more than pinCode at a time on table "auth".', 16, 1)
        ROLLBACK TRANSACTION
        RETURN
    END --IF

    UPDATE n
    SET pinCode = i.pinCode
    FROM nurseTable n
    CROSS JOIN inserted i
    INNER JOIN deleted d ON
        d.pinCode = n.pinCode

    UPDATE rn
    SET pinCode = i.pinCode
    FROM rnTable n
    CROSS JOIN inserted i
    INNER JOIN deleted d ON
        d.pinCode = n.pinCode

END --IF
0
 

Author Comment

by:s_hausen
ID: 39609778
hi ScottPletcher,
it works but it has updated all the pins of nurse table and rn table.  i only need to change the pin, which i'm updating.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39609872
Hmm, I figured the INNER JOIN on deleted would take care of that.  Please make sure the code you're using has an INNER JOIN to deleted and not a CROSS JOIN (which I had initially posted in my code but changed it to INNER JOIN to avoid updating all rows :-) ).

If the INNER JOIN is there, let me know and I will look further.

For these types of tables, you really should add an IDENTITY so old and new rows can be easily matched.
0
 

Author Comment

by:s_hausen
ID: 39610445
i made some changes in it and its working fine now. thanks for everyone..
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question