February 23, 2011 at 9:02 am
Greetings...
I am an intermediate SQL User - good at Databases, Tables, Stored Procedures and such - but I am driving myself crazy trying to write an effective Trigger.
Here is my situation: I have two Tables. When the first Table is INSERTED or UPDATED, I simply want those changes to affect a second Table that is very similar to the first (at least in the columns I am trying get updated...)
Here is how I coded my trigger...
ALTER TRIGGER [dbo].[Update2ndTable]
ON [dbo].[Table1]
AFTER INSERT, UPDATE
AS
DECLARE @UserName nchar(25) , @EmailName nchar(64) , @FolderPrefix nchar(50)
SELECT @UserName = UserName FROM dbo.Table1
SELECT @EmailName = EmailName FROM dbo.Table1
SELECT @FolderPrefix = UploadPath FROM dbo.ConfigTable
IF NOT EXISTS (SELECT UserName FROM Table2 WHERE (UserName = @UserName))
INSERT INTO [dbo].[Table2] (Username, Emailaddr , Folder)
VALUES (@UserName, @EmailName, RTRIM(@FolderPrefix) + '\' + RTRIM(@UserName)+ '\Downloads')
ELSE
UPDATE [dbo].[Table2]
SET EmailAddr = @EmailName , Folder = RTRIM(@FolderPrefix) + '\' + RTRIM(@UserName)+ '\Downloads'
WHERE (UserName = @UserName)
As you (may) see, I am just getting values out of the first Table and on an INSERT trying to pass those into the second Table, and on an UPDATE, just trying to update the second Table.
BOTH Tables have UserName as a Primary key.
However, when I run this I get some very odd results - usually it just duplicates values in the FIRST table - not even the Table I am trying to update! Then when I ran this called from a VB.NET loop, I wound up with just one record getting updated (in the second table), and the first table got completely duplicated!!!
No question - I have botched this one pretty good. Anybody out there have some "un-botching" suggestions for me?
Thanks very much.
February 23, 2011 at 9:24 am
Do Table1 and ConfigTable only have 1 row? If not, then this will never work the way I think you want it to.
Whenever you do an insert or an update in SQL Server, the new values are written to the INSERTED table. You can get the values from there as opposed to the table that you are creating the trigger on.
You could then do something of the form
INSERT INTO table2(cola,colb,colc)
values (INSERTED.cola,INSERTED.colb,INSERTED.colc)
or
UPDATE [dbo].[Table2]
SET cola = INSERTED.cola , colb = INSERTED.colb
WHERE (UserName = INSERTED.UserName)
Without knowing more about how you select from ConfigTable the above is the best that I can recomment. It should get you started though. I recomment that you look up the INSERTED and DELETED tables in BOL. It'll help you debotch;)
February 23, 2011 at 9:25 am
ok, you should be able to streamline your trigger, and configure it for multiple rows as well.
see if this makes sence to you:
ALTER TRIGGER [dbo].[Update2ndTable]
ON [dbo].[Table1]
AFTER INSERT, UPDATE
AS
--if there is deleted data, it is an UPDATE, else it is an insert
IF NOT EXISTS(SELECT 1 FROM DELETED)
BEGIN
INSERT INTO [dbo].[Table2] (Username, Emailaddr , Folder)
SELECT Username,EmailName,RTRIM(D.UploadPath) + '\' + RTRIM(D.UserName)+ '\Downloads'
FROM INSERTED D
END
ELSE
BEGIN
UPDATE [dbo].[Table2]
SET EmailAddr = D.EmailName , Folder = RTRIM(D.UploadPath) + '\' + RTRIM(D.UserName)+ '\Downloads'
FROM INSERTED D
WHERE [dbo].[Table2].UserName = D.UserName
END
Lowell
February 23, 2011 at 9:26 am
There could be both inserts and updates:
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Update2ndTable]
ON [dbo].[Table1]
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON
UPDATE T2
SET EmailAddr = I.EmailAddr
,Folder = RTRIM(UploadPath) + '\' + RTRIM(UserName)+ '\Downloads'
FROM [dbo].[Table2] T2
JOIN inserted I
ON T2.UserName = I.UserName
INSERT INTO [dbo].[Table2] (Username, Emailaddr , Folder)
SELECT UserName, EmailName, RTRIM(UploadPath) + '\' + RTRIM(UserName)+ '\Downloads'
FROM inserted I
WHERE NOT EXISTS
(
SELECT *
FROM [dbo].[Table2] T2 WITH (SERIALIZABLE)
WHERE I.UserName = T2.UserName
)
GO
February 23, 2011 at 9:27 am
Start with this. It should shed a lot of light (if not all of it) on what's wrong with your code.
http://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/
February 23, 2011 at 9:28 am
Try something like this:
ALTER TRIGGER [dbo].[Update2ndTable] ON [dbo].[Table1]
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON ; -- Can be very important in triggers
UPDATE T2
SET EmailAddr = EmailName,
Folder = RTRIM(inserted.FolderPrefix) + '\'
+ RTRIM(inserted.UserName) + '\Downloads'
FROM dbo.Table2 AS T2
INNER JOIN inserted
ON T2.UserName = inserted.Username
AND (T2.EmailAddr != INSERTED.EmailName
OR T2.Folder != RTRIM(inserted.FolderPrefix) + '\'
+ RTRIM(inserted.UserName) + '\Downloads') ;
INSERT INTO [dbo].[Table2]
(Username,
Emailaddr,
Folder)
SELECT UserName,
EmailName,
RTRIM(FolderPrefix) + '\' + RTRIM(UserName) + '\Downloads'
FROM inserted
WHERE Username NOT IN (SELECT Username
FROM dbo.Table2
WHERE Username IS NOT NULL) ;
The key difference is selecting from "inserted", not from "Table1".
The version you wrote just assigns values to the variables from a semi-random row in Table1, not necessarily from the row you're updating or inserting.
Also, using variables, even from the "inserted" table in a trigger, limits it to single-row operations. The trigger will do "weird" things if you ever update more than one row at a time, or insert multiple rows.
The re-written version will work with multi-row insert/update commands, and will work with the exact rows inserted/updated.
You'll need to confirm I got the columns right, since I don't have your actual table definitions. The columns in "inserted" will match the columns in Table1.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 23, 2011 at 9:31 am
I see a couple of issues:
1) You're assuming only one row gets inserted/updated at a time.
2) You're assigning to variables read from the source table (Table1) and not from the logical table INSERTED
Try this instead:
ALTER TRIGGER [dbo].[Update2ndTable]
ON [dbo].[Table1]
AFTER INSERT, UPDATE
AS
BEGIN
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
DECLARE @FolderPrefix nchar(50)
SELECT @FolderPrefix = UploadPath FROM dbo.ConfigTable
BEGIN TRANSACTION
BEGIN TRY
UPDATE T2
SET EmailAddr = EmailName , Folder = RTRIM(@FolderPrefix) + '\' + RTRIM(UserName)+ '\Downloads'
FROM [dbo].[Table2] T2
INNER JOIN INSERTED T1
ON T2.UserName = T1.UserName
INSERT INTO [dbo].[Table2] (Username, Emailaddr , Folder)
SELECT (UserName, EmailName, RTRIM(@FolderPrefix) + '\' + RTRIM(UserName)+ '\Downloads')
FROM INSERTED T1
WHERE NOT EXISTS (
SELECT 1
FROM [dbo].[Table2] T2
WHERE T2.UserName = T1.UserName
)
COMMIT
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()
IF @@TRANCOUNT > 0
ROLLBACK TRAN
RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH
END
Hope this helps
Gianluca
-- Gianluca Sartori
February 23, 2011 at 9:38 am
Well, amazing! 😀
5 posters = 5 different versions of the same trigger!
You should be able to merge everything into a single trigger...;-)
-- Gianluca Sartori
February 23, 2011 at 9:41 am
Thanks to all you guys for your replies! Very VERY helpful!
In the particulars - Yes, my Config table has (and will forever have) only one row.
I see the error of my ways in using vrbls instead of "inserted" - great tip, thank you!
Lowell, there will NEVER be any deleted records, but I think I see what you are doing with your suggestion that way, eg, test for update that way, yes?
I will take all this in and start some reworks. Again, to all of you, many many thanks - very helpful...
...I dont know if its sad, or amazing, or both that I get a bundle of great, informative answers on a free forum, and I get them pronto!!! NO other forum or support venue does, or has ever done that.
I am in your debt guys - again many thanks!
February 23, 2011 at 9:54 am
blandry (2/23/2011)
Lowell, there will NEVER be any deleted records, but I think I see what you are doing with your suggestion that way, eg, test for update that way, yes?
I love this forum too;
just for an explanation: inside a trigger, there are two virtual tables that exist only for the duration of a triggers execution : INSERTED and DELETED; those two tables have the EXACT same layout as the table the trigger is on...but they have the identity() values, and calculated values for any calculated columns already for consumption.
For an insert trigger, the INSERTED table has all the new values, and the DELETED table is null/empty
For an UPDATE trigger INSERTED table has all the new values, and the DELETED table has the values that are being replaced. (ie name columns INSERT = 'Robert' and DELETED = 'Bob', because we did UPDATE MyTable SET name = 'Robert' where id = x
for a DELETE trigger, it's similar to the insert trigger... the INSERTED table is empty, and the DELETED table has the values that are about to be destroyed.
my example simply tested if there was any data in that DELETED table...if there is, we know the trigger operation HAD to have been an update (since it was defined as for INSERT,UPDATE)
Lowell
February 23, 2011 at 9:55 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 23, 2011 at 9:57 am
Lowell, Thanks - I kind of presumed that was what was behind it - but I really appreciate you spelling that out. Thanks for sharing the knowledge and expertise!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply