Solved

update trigger in sql server

Posted on 2013-10-28
7
602 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
[X]
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
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

752 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