Solved

update trigger in sql server

Posted on 2013-10-28
7
590 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

786 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