[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

From a trigger calling a stored procedure to update the last change

Hi Experts,

I have made a trigger on an insert from table 1.
This trigger is calling a stored procedure. From table 1 I need some values to
pass to the sp. How can I store these values in the trigger and pass them to
the sp.
In the stored procedure I have made an update for table 2 and I need the
values that are coming from the trigger.
The trigger is working and the sp also but the sp is updating every field in table 2
and not the field with the value that was updated in table 1.
0
dgb
Asked:
dgb
  • 3
  • 3
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
there is a INSERTED table available in the trigger that holds the new records inserted, however, that table is NOT visible from the procedures you would be calling.
so, you should either put the procedure code directly into the trigger, or create a cursor on the INSERTED table to call the procedure with the values.
0
 
StephenCairnsCommented:
or (if its a single record process) pass the values from inserted as parameters to the stored procedure
0
 
dgbAuthor Commented:
if I use INSERTED in the trigger it is not recognized.
How do I create a cursor on the inserted field.
How do I put the values to some variables and pass them to the
procedure I am calling.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
StephenCairnsCommented:
if its a single record
select @1stparam = 1stfield, @2ndparam = 2ndfield from inserted

0
 
dgbAuthor Commented:
inserted is nog recognized.

I have the following code:

create trigger insert_klant on dbo.klant
for insert
as
    insert into klantinsert
    values(user, getdate(),NULL,'I')

NULL = nummer from klant. Ik I put nummer instead I have a message that the name
nummer is not permitted. If I use the word inserted it gives an incorrect syntax.

0
 
StephenCairnsCommented:
create trigger insert_klant on dbo.klant
for insert
as
    insert into klantinsert (user, date, something, somethingelse)
    select user, getdate(),NULL,'I' from inserted

a bit of background
inserted is a storage area that sql  uses when its inserting or altering a table.
i holds all the fields of the table in question
There is a second storage area deleted much the same
these storage areas are only available in the trigger
when an insert is performed the record is held put into the Inserted storage area before its put into the table
when an update is performed the oriigonal record is put in Deleted storage area and the new record put in the Inserted storage area
When a delete is performed then the deleted record is put in the Deleted storage area


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
a minor correction on StepenCairns, as you wanted to use the nummer column from the klant table (ie inserted)

create trigger insert_klant on dbo.klant
for insert
as
    insert into klantinsert (user, date, something, somethingelse)
    select user, getdate(),nummer,'I' from inserted

0
 
dgbAuthor Commented:
sorry for the late reaction, been a bit busy.

Thanks
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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