July 18, 2014 at 6:34 am
Hello All,
I would like to change a value after the table updates \ inserts a new row based on the value of that column
Table Name is: MPanel
Col Names: RID, FPID.
I want to:
If column RID='16'
Then UPDATE FPID='1'
How can I trigger that update? Would this work:???
CREATE TRIGGER dbo.MyTrigLI
ON dbo.MPanel
FOR INSERT, UPDATE
AS
BEGIN
UPDATE [MPanel]
SET
FPID='1'
WHERE (UPDATE(RID='16')
END
Many thank yous in advance
July 18, 2014 at 7:19 am
npittson (7/18/2014)
Hello All,I would like to change a value after the table updates \ inserts a new row based on the value of that column
Table Name is: MPanel
Col Names: RID, FPID.
I want to:
If column RID='16'
Then UPDATE FPID='1'
How can I trigger that update? Would this work:???
CREATE TRIGGER dbo.MyTrigLI
ON dbo.MPanel
FOR INSERT, UPDATE
AS
BEGIN
UPDATE [MPanel]
SET
FPID='1'
WHERE (UPDATE(RID='16')
END
Many thank yous in advance
1) Take a look at Books Online for information about triggers, and especially note the INSERTED and DELETED tables.
2) You will need to join INSERTED to MPanel on it's PK and do the update with WHERE clause that way.
3) Also check about the UPDATE() function so you can test if RID was changed and not waste time with your UPDATE statement if it wasn't.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 18, 2014 at 7:45 am
CREATE TRIGGER [dbo].[MyTrigLI]
ON [dbo].[MPanel]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
Update M
set M.FPID = '1'
From MPanel M
Inner Join INSERTED I On I.ID = M.ID
WHERE RID='16'
END
you can have trigger like this to use the inserted column, here we need to have an ID column to identify the row from the inserted column. what could be the other values for FPID , in case if it is some default you can use computed columns.
July 18, 2014 at 8:03 am
Thanks for your reply.
After I posting I began scouring the net trying to put something together, in the end I found this worked for me:
CREATE TRIGGER dbo.MyTrigLI ON dbo.MPanel
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
IF (SELECT COUNT(*) FROM inserted WHERE RID='16') = 1
UPDATEmt
SETmt.FPID = '1'
FROMdbo.MPanel AS mt
INNER JOINinserted AS i ON i.PKCol = mt.PKCol
END
GO
July 18, 2014 at 8:55 am
npittson (7/18/2014)
Thanks for your reply.After I posting I began scouring the net trying to put something together, in the end I found this worked for me:
CREATE TRIGGER dbo.MyTrigLI ON dbo.MPanel
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
IF (SELECT COUNT(*) FROM inserted WHERE RID='16') = 1
UPDATEmt
SETmt.FPID = '1'
FROMdbo.MPanel AS mt
INNER JOINinserted AS i ON i.PKCol = mt.PKCol
END
GO
Oh, this is NOT GOOD!!
1) NEVER count everything when you just want EXISTENCE! Suppose it were a billion rows - would you really want to count all of them??
2) What if TWO or more rows were 16?? You would NOT update them!! Triggers fire PER BATCH, not PER ROW?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 21, 2014 at 1:48 am
[/quote]
Oh, this is NOT GOOD!!
1) NEVER count everything when you just want EXISTENCE! Suppose it were a billion rows - would you really want to count all of them??
2) What if TWO or more rows were 16?? You would NOT update them!! Triggers fire PER BATCH, not PER ROW?
[/quote]
Thanks Kevin, thats good advice because I dont know much about this at all. Luckily that particular table can only ever have a max of 65 rows and would usually be less (it is based on a group of people), also because of a system that it is linked to, I would only expect that there be one row written at a time. So although its not good practice it might work. However, might is not best, so what would be best practice in this situation? Would sharath.chalamgari response be better?
July 21, 2014 at 2:03 am
npittson (7/18/2014)
Thanks for your reply.After I posting I began scouring the net trying to put something together, in the end I found this worked for me:
CREATE TRIGGER dbo.MyTrigLI ON dbo.MPanel
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
IF (SELECT COUNT(*) FROM inserted WHERE RID='16') = 1
UPDATEmt
SETmt.FPID = '1'
FROMdbo.MPanel AS mt
INNER JOINinserted AS i ON i.PKCol = mt.PKCol
END
GO
Waooo, you are updating the complete table, i think you have missed the where clause in the end.
2ndly, i these situation you should use EXISTS. Like this
CREATE TRIGGER dbo.MyTrigLI ON dbo.MPanel
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM inserted WHERE RID='16')
UPDATEmt
SETmt.FPID = '1'
FROMdbo.MPanel AS mt
INNER JOINinserted AS i ON i.PKCol = mt.PKCol
WHERE RID='16';
END
GO
July 22, 2014 at 3:40 am
Thanks for your help, I will try to test that later today 🙂
July 23, 2014 at 2:35 am
Thanks a million everyone for your help. You people are the best 🙂
July 23, 2014 at 3:44 am
glad to help
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply