Solved

update trigger in sql server

Posted on 2013-10-28
7
586 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 69

Accepted Solution

by:
ScottPletcher 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:ScottPletcher
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now