February 16, 2017 at 2:13 pm
Hi ,
I created a instead of update trigger . Something like below. Can this be modified and instead of two updates , can i get it to write one update ? if so how?
create trigger
as Instead of trigger
BEGin
IF Update(code1)
Begin
Declare @var1
SELECT @Var1 = [A].CODE1
FROM [mdm].[Currency] AS [M]
JOIN [INSERTED] AS ON [M].[Code] = .[Currency_Code]
if (@var1 is null)
Begin
Raiseerror(
END
Update TableA
Set Code1=@var1
END
IF Update(code2)
Begin
Declare @var2
SELECT @Var2 = [A].code2
FROM [mdm].[region] AS [M]
JOIN [INSERTED] AS ON [M].[Code] = .[region_Code]
if (@var2 is null)
Begin
Raiseerror
END
Update TableA
Set Code2=@var2
END
END
February 16, 2017 at 2:18 pm
komal145 - Thursday, February 16, 2017 2:13 PMHi ,
I created a instead of update trigger . Something like below. Can this be modified and instead of two updates , can i get it to write one update ? if so how?create trigger
as Instead of trigger
BEGin
IF Update(code1)
Begin
Declare @var1
SELECT @Var1 = [A].CODE1
FROM [mdm].[Currency] AS [M]
JOIN [INSERTED] AS ON [M].[Code] = .[Currency_Code]if (@var1 is null)
Begin
Raiseerror(
END
Update TableA
Set Code1=@var1
ENDIF Update(code2)
Begin
Declare @var2
SELECT @Var2 = [A].code2
FROM [mdm].[region] AS [M]
JOIN [INSERTED] AS ON [M].[Code] = .[region_Code]
if (@var2 is null)
Begin
Raiseerror
END
Update TableA
Set Code2=@var2
ENDEND
This trigger will fail if more than one row is inserted, it needs a redesign to accommodate that.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 16, 2017 at 2:22 pm
Phil Parkin - Thursday, February 16, 2017 2:18 PMkomal145 - Thursday, February 16, 2017 2:13 PMHi ,
I created a instead of update trigger . Something like below. Can this be modified and instead of two updates , can i get it to write one update ? if so how?create trigger
as Instead of trigger
BEGin
IF Update(code1)
Begin
Declare @var1
SELECT @Var1 = [A].CODE1
FROM [mdm].[Currency] AS [M]
JOIN [INSERTED] AS ON [M].[Code] = .[Currency_Code]if (@var1 is null)
Begin
Raiseerror(
END
Update TableA
Set Code1=@var1
ENDIF Update(code2)
Begin
Declare @var2
SELECT @Var2 = [A].code2
FROM [mdm].[region] AS [M]
JOIN [INSERTED] AS ON [M].[Code] = .[region_Code]
if (@var2 is null)
Begin
Raiseerror
END
Update TableA
Set Code2=@var2
ENDEND
This trigger will fail if more than one row is inserted, it needs a redesign to accommodate that.
This is to do the update . When ever user tries to update any code , it will do validate the code and if error it will throw error message
February 16, 2017 at 2:27 pm
Your first problem is that you may not understand how triggers work, as your trigger pseudo code looks like it's only going to be able to handle single record updates, and clearly, an UPDATE statement can affect multiple records, and so when the trigger fires, multiple records will be in the INSERTED and DELETED pseudo tables. If you expect the trigger to work correctly, it has to be able to handle ANY number of records, and not just one. Do you really need a trigger? It's going to be a lot more complex than you planned, and it looks more like this might be an architecture problem, but I don't really have enough information to come to any conclusions. As you didn't provide anything but the pseudo code for it, I don't really have any idea what your ultimate objective is, so there's literally very little useful guidance here to provide, beyond asking questions about what you want to accomplish, and why...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 16, 2017 at 2:30 pm
komal145 - Thursday, February 16, 2017 2:22 PMPhil Parkin - Thursday, February 16, 2017 2:18 PMkomal145 - Thursday, February 16, 2017 2:13 PMHi ,
I created a instead of update trigger . Something like below. Can this be modified and instead of two updates , can i get it to write one update ? if so how?create trigger
as Instead of trigger
BEGin
IF Update(code1)
Begin
Declare @var1
SELECT @Var1 = [A].CODE1
FROM [mdm].[Currency] AS [M]
JOIN [INSERTED] AS ON [M].[Code] = .[Currency_Code]if (@var1 is null)
Begin
Raiseerror(
END
Update TableA
Set Code1=@var1
ENDIF Update(code2)
Begin
Declare @var2
SELECT @Var2 = [A].code2
FROM [mdm].[region] AS [M]
JOIN [INSERTED] AS ON [M].[Code] = .[region_Code]
if (@var2 is null)
Begin
Raiseerror
END
Update TableA
Set Code2=@var2
ENDEND
This trigger will fail if more than one row is inserted, it needs a redesign to accommodate that.
This is to do the update . When ever user tries to update any code , it will do validate the code and if error it will throw error message
A trigger isn't necessary for that. Design the tables to have a foreign-key relationships to lookup tables that have all the valid codes. Then any INSERT that doesn't have a valid code will get rejected.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 16, 2017 at 2:45 pm
sgmunson - Thursday, February 16, 2017 2:27 PMYour first problem is that you may not understand how triggers work, as your trigger pseudo code looks like it's only going to be able to handle single record updates, and clearly, an UPDATE statement can affect multiple records, and so when the trigger fires, multiple records will be in the INSERTED and DELETED pseudo tables. If you expect the trigger to work correctly, it has to be able to handle ANY number of records, and not just one. Do you really need a trigger? It's going to be a lot more complex than you planned, and it looks more like this might be an architecture problem, but I don't really have enough information to come to any conclusions. As you didn't provide anything but the pseudo code for it, I don't really have any idea what your ultimate objective is, so there's literally very little useful guidance here to provide, beyond asking questions about what you want to accomplish, and why...
My provided Update is just an example:
My actual code update :
UPDATE TABLE A
SET CODE1=.CODE1
FROM INSERTED
INNSER JOIN TABLEA [ A] ON A.id=I.id
Yes this will only update one record at a time. Not multiple records. As the table has only unique records.
February 16, 2017 at 2:59 pm
komal145 - Thursday, February 16, 2017 2:45 PMsgmunson - Thursday, February 16, 2017 2:27 PMYour first problem is that you may not understand how triggers work, as your trigger pseudo code looks like it's only going to be able to handle single record updates, and clearly, an UPDATE statement can affect multiple records, and so when the trigger fires, multiple records will be in the INSERTED and DELETED pseudo tables. If you expect the trigger to work correctly, it has to be able to handle ANY number of records, and not just one. Do you really need a trigger? It's going to be a lot more complex than you planned, and it looks more like this might be an architecture problem, but I don't really have enough information to come to any conclusions. As you didn't provide anything but the pseudo code for it, I don't really have any idea what your ultimate objective is, so there's literally very little useful guidance here to provide, beyond asking questions about what you want to accomplish, and why...My provided Update is just an example:
My actual code update :
UPDATE TABLE A
SET CODE1=.CODE1
FROM INSERTED
INNER JOIN TABLEA AS A
ON A.id=I.idYes this will only update one record at a time. Not multiple records. As the table has only unique records.
Perhaps not the way you think. Every matching record in the inserted table will result in an UPDATE to TABLEA. It could be the same record, but the number of rows updated will still be equal to the number of rows in the INSERTED pseudo table that have a match in the destination table.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 16, 2017 at 3:24 pm
sgmunson - Thursday, February 16, 2017 2:59 PMkomal145 - Thursday, February 16, 2017 2:45 PMsgmunson - Thursday, February 16, 2017 2:27 PMYour first problem is that you may not understand how triggers work, as your trigger pseudo code looks like it's only going to be able to handle single record updates, and clearly, an UPDATE statement can affect multiple records, and so when the trigger fires, multiple records will be in the INSERTED and DELETED pseudo tables. If you expect the trigger to work correctly, it has to be able to handle ANY number of records, and not just one. Do you really need a trigger? It's going to be a lot more complex than you planned, and it looks more like this might be an architecture problem, but I don't really have enough information to come to any conclusions. As you didn't provide anything but the pseudo code for it, I don't really have any idea what your ultimate objective is, so there's literally very little useful guidance here to provide, beyond asking questions about what you want to accomplish, and why...My provided Update is just an example:
My actual code update :
UPDATE TABLE A
SET CODE1=.CODE1
FROM INSERTED
INNER JOIN TABLEA AS A
ON A.id=I.idYes this will only update one record at a time. Not multiple records. As the table has only unique records.
Perhaps not the way you think. Every matching record in the inserted table will result in an UPDATE to TABLEA. It could be the same record, but the number of rows updated will still be equal to the number of rows in the INSERTED pseudo table.
So did you mean if i insert a new record say "120" ID ( primary key for record) and then try to update a record 119 : region code =USA ? it will update the 120 id region code?
February 16, 2017 at 3:28 pm
komal145 - Thursday, February 16, 2017 3:24 PMsgmunson - Thursday, February 16, 2017 2:59 PMkomal145 - Thursday, February 16, 2017 2:45 PMsgmunson - Thursday, February 16, 2017 2:27 PMYour first problem is that you may not understand how triggers work, as your trigger pseudo code looks like it's only going to be able to handle single record updates, and clearly, an UPDATE statement can affect multiple records, and so when the trigger fires, multiple records will be in the INSERTED and DELETED pseudo tables. If you expect the trigger to work correctly, it has to be able to handle ANY number of records, and not just one. Do you really need a trigger? It's going to be a lot more complex than you planned, and it looks more like this might be an architecture problem, but I don't really have enough information to come to any conclusions. As you didn't provide anything but the pseudo code for it, I don't really have any idea what your ultimate objective is, so there's literally very little useful guidance here to provide, beyond asking questions about what you want to accomplish, and why...My provided Update is just an example:
My actual code update :
UPDATE TABLE A
SET CODE1=.CODE1
FROM INSERTED
INNER JOIN TABLEA AS A
ON A.id=I.idYes this will only update one record at a time. Not multiple records. As the table has only unique records.
Perhaps not the way you think. Every matching record in the inserted table will result in an UPDATE to TABLEA. It could be the same record, but the number of rows updated will still be equal to the number of rows in the INSERTED pseudo table.
So did you mean if i insert a new record say "120" ID ( primary key for record) and then try to update a record 119 : region code =USA ? it will update the 120 id region code?
From below link....it shows that inserted table will holde "updated record"
http://www.dotnettricks.com/learn/sqlserver/inserted-deleted-logical-table-in-sql-server
February 25, 2017 at 7:08 pm
komal145 - Thursday, February 16, 2017 3:24 PMsgmunson - Thursday, February 16, 2017 2:59 PMkomal145 - Thursday, February 16, 2017 2:45 PMsgmunson - Thursday, February 16, 2017 2:27 PMYour first problem is that you may not understand how triggers work, as your trigger pseudo code looks like it's only going to be able to handle single record updates, and clearly, an UPDATE statement can affect multiple records, and so when the trigger fires, multiple records will be in the INSERTED and DELETED pseudo tables. If you expect the trigger to work correctly, it has to be able to handle ANY number of records, and not just one. Do you really need a trigger? It's going to be a lot more complex than you planned, and it looks more like this might be an architecture problem, but I don't really have enough information to come to any conclusions. As you didn't provide anything but the pseudo code for it, I don't really have any idea what your ultimate objective is, so there's literally very little useful guidance here to provide, beyond asking questions about what you want to accomplish, and why...My provided Update is just an example:
My actual code update :
UPDATE TABLE A
SET CODE1=.CODE1
FROM INSERTED
INNER JOIN TABLEA AS A
ON A.id=I.idYes this will only update one record at a time. Not multiple records. As the table has only unique records.
Perhaps not the way you think. Every matching record in the inserted table will result in an UPDATE to TABLEA. It could be the same record, but the number of rows updated will still be equal to the number of rows in the INSERTED pseudo table.
So did you mean if i insert a new record say "120" ID ( primary key for record) and then try to update a record 119 : region code =USA ? it will update the 120 id region code?
I think you're missing the point. Triggers will fire on EVERY update to the table, regardless of the number of records involved. What would you do if someone asked you to change a large number of records to fix some values that are in error, and you were able to determine the criteria for the UPDATE statement. What would you expect the trigger to do? If multiple updates take place because of ONE update statement, the trigger fires ONLY ONCE, and then runs that code with the INSERTED and DELETED tables containing as many records as get changed by that one update statement. It does NOT fire the trigger on each individual record - only once for the ENTIRE update, so you have to handle ALL records updated in that one piece of trigger code, or it may not give you the results that you expect. Also, heaven forbid you "get hit by a truck", or are otherwise no longer employed at this company... are they going to know about this trigger at that level and know that if they ever make such an update that this trigger is sitting there, waiting to bite them in the backside?
To your question, NO, the trigger is not going to affect records that are not updated / inserted... but... you leave yourself open to anyone else that might NOT be familiar with the fact that this trigger is in place, and that it will NOT properly handle multiple record updates. You sure you want to expose your employer to that risk?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply