Prev: SQL: Spelling out numbers to words for month
Next: varbinary(max) field in sql server 2005 problems
From: sirrahhc on 11 Mar 2010 17:14
Hey guys, whats up?? I am not new to SQL but I am trying to create an Update
trigger for the first time. I have found several links to Update triggers
but they seemed to confuse me more than anything. So, here's what I'm trying
to do. I have a table called, "MattersQLegalAssistant" (Table 1)that has a
field called "Q1stLABridgelin" (Field 1). I am trying to update a field
called, "Q1stBridgeline" (Field 2) in a 2nd table called,
"MattersQMSUHearing1" (Table 2). So in other words, Update Table 2, Field 2
with values from Table 1, Field 1 if Field 1 is updated. I thought this
should be pretty simple but like I said the examples I've come across have
kind of confused me more than I was. I tried the following only to fail
however:

CREATE TRIGGER [dbo].[MattersQMSUHearing1_BridgelinUpdate]
ON [dbo].[MattersQMSUHearing1]
FOR Update AS
Update [MattersQMSUHearing1]
set Q1stBridgeline = Q2.Q1stLABridgelin
from [MattersQMSUHearing1]
inner join inserted
on inserted.matters = [MattersQMSUHearing1].matters
Inner join MattersQLegalAssistant Q2
on Q2.Matters = [MattersQMSUHearing1].Matters

...I know what I have above is quite wrong but not sure how to remedy. Can
anyone help me out please?? I would greatly appreciate it, thanks!!

From: Erland Sommarskog on 11 Mar 2010 17:47
sirrahhc (u58702(a)uwe) writes:
> Hey guys, whats up?? I am not new to SQL but I am trying to create an
> Update trigger for the first time. I have found several links to Update
> triggers but they seemed to confuse me more than anything. So, here's
> what I'm trying to do. I have a table called, "MattersQLegalAssistant"
> (Table 1)that has a field called "Q1stLABridgelin" (Field 1). I am
> trying to update a field called, "Q1stBridgeline" (Field 2) in a 2nd
> table called, "MattersQMSUHearing1" (Table 2). So in other words,
> Update Table 2, Field 2 with values from Table 1, Field 1 if Field 1 is
> updated. I thought this should be pretty simple but like I said the
> examples I've come across have kind of confused me more than I was. I
> tried the following only to fail however:
>
> CREATE TRIGGER [dbo].[MattersQMSUHearing1_BridgelinUpdate]
> ON [dbo].[MattersQMSUHearing1]
> FOR Update AS
> Update [MattersQMSUHearing1]
> set Q1stBridgeline = Q2.Q1stLABridgelin
> from [MattersQMSUHearing1]
> inner join inserted
> on inserted.matters = [MattersQMSUHearing1].matters
> Inner join MattersQLegalAssistant Q2
> on Q2.Matters = [MattersQMSUHearing1].Matters
>
> ..I know what I have above is quite wrong but not sure how to remedy. Can
> anyone help me out please?? I would greatly appreciate it, thanks!!

If you want to update a row in MattersQMSUHearing1 when a value in
MattersQLegalAssistant is updated, then you should have a trigger
on MattersQLegalAssistant, but you have in fact defined the trigger
to on MattersQMSUHearing1.

Given you narrative, I would expect:

CREATE TRIGGER tri ON MattersQLegalAssistant FOR UPDATE AS
UPDATE MattersQMSUHearing1
SET Q1stBridgeline = i.Q1stLABridgelin
FROM MattersQMSUHearing1 H1
JOIN inserted i ON H1.matters = i.matters

Well, you don't say how the tables are related, so I will have to
rely on your code that matters is a key in both tables.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Tom Cooper on 11 Mar 2010 18:08
You want the trigger to be on the table that the original update is done to,
that is, on MattersQLegalAssistant. And there is no need to go to the
original table in your update statement in this case, just using the
inserted psuedo table is sufficient. Something like (not tested)

Create Trigger dbo.MattersQLegalAssistantUpdate
On dbo.MattersQLegalAssistant
For Update As
Update h
Set Q1stBridgeline = i.Q1stBridgelin
From dbo.MattersQMSUHearing1 h
Inner Join inserted i On h.Matters = i.Matters;

Tom

"sirrahhc" <u58702(a)uwe> wrote in message news:a4de2670b25a7(a)uwe...
> Hey guys, whats up?? I am not new to SQL but I am trying to create an
> Update
> trigger for the first time. I have found several links to Update triggers
> but they seemed to confuse me more than anything. So, here's what I'm
> trying
> to do. I have a table called, "MattersQLegalAssistant" (Table 1)that has
> a
> field called "Q1stLABridgelin" (Field 1). I am trying to update a field
> called, "Q1stBridgeline" (Field 2) in a 2nd table called,
> "MattersQMSUHearing1" (Table 2). So in other words, Update Table 2, Field
> 2
> with values from Table 1, Field 1 if Field 1 is updated. I thought this
> should be pretty simple but like I said the examples I've come across have
> kind of confused me more than I was. I tried the following only to fail
> however:
>
> CREATE TRIGGER [dbo].[MattersQMSUHearing1_BridgelinUpdate]
> ON [dbo].[MattersQMSUHearing1]
> FOR Update AS
> Update [MattersQMSUHearing1]
> set Q1stBridgeline = Q2.Q1stLABridgelin
> from [MattersQMSUHearing1]
> inner join inserted
> on inserted.matters = [MattersQMSUHearing1].matters
> Inner join MattersQLegalAssistant Q2
> on Q2.Matters = [MattersQMSUHearing1].Matters
>
> ..I know what I have above is quite wrong but not sure how to remedy. Can
> anyone help me out please?? I would greatly appreciate it, thanks!!
>

From: sirrahhc via SQLMonster.com on 12 Mar 2010 09:18
Hey guys, thank you so much for the help. I've created this in the DB but it
is still not updating however. I get what your saying about putting the
trigger on the table that the update is based off of, that makes perfect
sense. And you guys were exactly right BTW, the way the two tables relate to
one another is through the Matters field. This looks like it should work too,
I just cannot wrap my head around where the error is...I'm going to keep
poking around with this, if you guys can think of where the error might be
please let me know. Again, thank you guys for your time!!

Tom Cooper wrote:
>You want the trigger to be on the table that the original update is done to,
>that is, on MattersQLegalAssistant. And there is no need to go to the
>original table in your update statement in this case, just using the
>inserted psuedo table is sufficient. Something like (not tested)
>
>Create Trigger dbo.MattersQLegalAssistantUpdate
>On dbo.MattersQLegalAssistant
>For Update As
>Update h
>Set Q1stBridgeline = i.Q1stBridgelin
>From dbo.MattersQMSUHearing1 h
>Inner Join inserted i On h.Matters = i.Matters;
>
>Tom
>
>> Hey guys, whats up?? I am not new to SQL but I am trying to create an
>> Update
>[quoted text clipped - 25 lines]
>> ..I know what I have above is quite wrong but not sure how to remedy. Can
>> anyone help me out please?? I would greatly appreciate it, thanks!!

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201003/1

From: sirrahhc via SQLMonster.com on 12 Mar 2010 09:35
Hey guys, I guess I need to correct myself. The trigger is working, but only
on the back end. When I change the field in the LegalAssistant table it does
indeed update the MSUHearing1 table, but only on the back end. When I pull
up the app and look it is not updating on the frontend. Weird....I will
continue to play with and let you guys know what I find, thanks!!

sirrahhc wrote:
>Hey guys, thank you so much for the help. I've created this in the DB but it
>is still not updating however. I get what your saying about putting the
>trigger on the table that the update is based off of, that makes perfect
>sense. And you guys were exactly right BTW, the way the two tables relate to
>one another is through the Matters field. This looks like it should work too,
>I just cannot wrap my head around where the error is...I'm going to keep
>poking around with this, if you guys can think of where the error might be
>please let me know. Again, thank you guys for your time!!
>
>>You want the trigger to be on the table that the original update is done to,
>>that is, on MattersQLegalAssistant. And there is no need to go to the
>[quoted text clipped - 16 lines]
>>> ..I know what I have above is quite wrong but not sure how to remedy. Can
>>> anyone help me out please?? I would greatly appreciate it, thanks!!

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201003/1

 |  Next  |  Last
Pages: 1 2
Prev: SQL: Spelling out numbers to words for month
Next: varbinary(max) field in sql server 2005 problems