Solved

update trigger in sql server

Posted on 2013-10-28
7
596 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

740 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