• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 417
  • Last Modified:

update table with a from statement.

Hi,

I want to update a table in a trigger, fired after an insert. I'm trying to get the following working ...

ALTER TRIGGER [dbo].[name]
   ON  [dbo].[table]
   AFTER INSERT
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
              UPDATE table set
        table.startdate = S.enddate
      table.enddate = DATEADD(d,CAST(S.Q/S.V as int),S.Enddate)

              FROM
                   Inserted AS I INNER JOIN Stable as  S
                   ON I.column1
                      =
                      S.column2
END

In short: take the enddate from a row in table s, do some math with it to get a new enddate.

What is the solution?
thanks!
0
aatjan
Asked:
aatjan
  • 2
  • 2
  • 2
1 Solution
 
appariCommented:
try like this

ALTER TRIGGER [dbo].[name]
   ON  [dbo].[table]
   AFTER INSERT
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
              UPDATE table set
        startdate = S.enddate,
        enddate = DATEADD(d,CAST(S.Q/S.V as int),S.Enddate)

              FROM
                   table join Inserted AS I
          ON table.pkey= I.Pkey
      INNER JOIN Stable as  S
                   ON I.column1    =     S.column2
END
0
 
QlemoC++ DeveloperCommented:
You will have to include the join on the key column(s) of Inserted and Table, either as a WHERE condition, or by using the JOIN syntax in ON:
update T
set startdate = S.enddate,
      enddate = DATEADD(d,CAST(S.Q/S.V as int),S.Enddate)
from Table T join Inserted I on T.key1 = I.key1 and T.key2 = I.key2
join STable S on I.column1 = S.column2

Open in new window

If you do not have a unique combination of column values to determine the row to update, you will need an INSTEAD OF trigger, and insert the record with changed startdate and enddate.
0
 
aatjanAuthor Commented:
HI, the solution as provided by appari does compiles without a problem. I will test it within secs.

Just for understanding why it works:

Why a join of Table with Inserted? Both table and inserted do contain the same data (of course inserted only the data as inserted). I would say that logically having inserted joined with Stable should be sufficient to get the results as needed ...

thanks!
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
appariCommented:
if you want to update data in Table, you have to update the table Table not inserted table. inserted and deleted are sprecial tables available in triggers with data before and after the insert/update/delete operation. we have to join inserted table with table to update only those records inserted by the insert statement causing the trigger to fire.
0
 
QlemoC++ DeveloperCommented:
Consider the pseudo tables INSERTED and DELETED as temporay tables containing copies of the changed records. They are not views pointing to the changed data in the original table (would be difficult for deleted/updated records to point to prior values ...).
0
 
aatjanAuthor Commented:
Aha. Thanks. i see my mistake. I will award the points to appari because he was the first who provided me a solution. I'm sure that the solution as provided by Qlemo will work too.

@Qlemo: you learned us hockey and football, now you learning me db things!!


great!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now