ms sql server 2005 - after data hits s table pass parameters into a trigger

once data hits a table I need to launch a trigger...but how do I pass those values into the trigger so I can manipulate the data?

CREATE TRIGGER TR_TestTable_IUD
ON TestTable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
      EXEC dbo.sp_test_stored_procedure(@client, @product1, @product2)
END
GO
LVL 10
GlobaLevelProgrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ketansonejiCommented:
Once your trigger is invoked, your data is available in the inserted or deleted table. INSERTED/DELETED table are implicit temp tables available in the trigger

I am assuming the parameters that you need to pass to your stored procedures are columns in your table i.e.  client, product1, product2  are columns of the table in which you are inserting data.

You can fetch the values inside your trigger in the following way:

1) If you are doing an insert then you can query the inserted table.
SELECT @Client = client, @product1=product1,@product2=product2 FROM inserted

2) If you are doing an update then the old data would be in the deleted table and the new data would be in th inserted table
-- For fetching old data i.e. data before the update
SELECT @Client = client, @product1=product1,@product2=product2 FROM deleted

-- For fetching new data i.e. data after the update
SELECT @Client = client, @product1=product1,@product2=product2 FROM inserted

3) If you are doing deleting data then the data deleted would be available in the deleted table.
SELECT @Client = client, @product1=product1,@product2=product2 FROM deleted

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic Advisor, Page EditorCommented:
That is fine for a single entry in INSERTED / DELETED but they can contain multiple entries...

for Inserts the new values goes into the special system temp table INSERTED
for Updates the new values goes into the special system temp table INSERTED
for Updates the old values goes into the special system temp table DELETED
for Deletes the old values goes into the special system temp table DELETED

So, you need to LOOP through those entries in INSERTED and DELETED to capture all the changes.

You could check to see what the row counts where and do the code that ketansoneji has posted - that will work well for single rows

And/or you can populate a temp table and loop through that...




-- step 1 create our testtable

--drop table testtable
create table testtable (id int identity, client int, product1 int, product2 int) 
GO

-- step 2 create our test stored procedure

create procedure sp_test_stored_procedure(@mode varchar(10), @client int, @product1 int, @product2 int)
as
begin
 print @mode + ':' + convert(varchar,isnull(@client,''))+' '+convert(varchar,isnull(@product1,''))+' '+convert(varchar,isnull(@product2,''))
end
GO

-- step 3 test our stored procedure

exec sp_test_stored_procedure 'yep',1,2,3
GO

-- step 4 create our trigger

CREATE TRIGGER TR_TestTable_IUD
ON TestTable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN

      declare @i int
      declare @d int
      declare @j int
      declare @mode varchar(10), @client int, @product1 int, @product2 int

      set @i = (select count(*) from inserted)  -- might use to determine rowcounts
      set @d = (select count(*) from deleted)   -- might use to determine rowcounts

      select identity (int,1,1) as ID, mode, client, product1, product2
      into #tmp_load_data
      from (select distinct 'deleted' as mode, client, product1, product2 from deleted 
      union all
      select distinct 'inserted' as mode, client, product1, product2 from inserted) s

      set @j = @@rowcount
      set @i = 1

      while @i <= @j
      begin
          
          select @mode = mode, @client = client, @product1 = product1, @product2 = product2 from #tmp_load_data where id = @i
          EXEC dbo.sp_test_stored_procedure @mode, @client, @product1, @product2
          set @i = @i + 1
      end

END
GO 

-- OK now just play and check the messages tab

insert testtable values (1,2,3)
GO

insert testtable values (1,5,4)
GO

update testtable set product2 = 7 where client = 1
GO

delete testtable where product1 = 2
GO

-- and dont forget to clean up after

--drop table testtable
--drop procedure sp_test_stored_procedure

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.