Pass Variables from Trigger to SP ?

Fairly new to triggers and stored procedures and so far I haven't had to pass any variables or even call an SP from a trigger. The problem I have is to pass a 'callid' variable from an insert trigger to a stored procedure.

The SP is to delete any rows that already exist that contain the passed 'callid' and then insert the new inserted data (using variables again hopefully).

Is this at all possible and if so what is the syntax for passing the variable from the trigger and then reading it into the stored procedure ?

TIASomething like:

CREATE TRIGGER trg_mytrigger ON tbl_my_table INSEAD OF INSERT
AS

DECLARE @.callid INT

SELECT @.callid = callid FROM inserted

--At this point, you can just to the delete from here, rather than calling a seperate sp_, so here are both ways:
EXEC sp_delete_rows_by_callid @.callid

--or
DELETE tbl_my_table WHERE callid = @.callid

--then do the insert - (you may want to be more explicit with field names here)

INSERT tbl_my_table
SELECT * FROM inserted

--end of trigger

sp_code:

CREATE PROCEDURE sp_delete_rows_by_callid
@.callid INT
AS
DELETE tbl_my_table WHERE callid = @.callid

-b|||Thanks bpdWork. I have modified the trigger as you suggested but I get an ODBC 3146 database error. Can you see where I may have got the syntax wrong. Thx.

CREATE TRIGGER trg_mytrigger ON callshistory INSTEAD OF INSERT
AS

DECLARE @.callid INT

SELECT @.callid = fkey FROM inserted as ins where ins.actiontext = 'hold' and ins.Subactiontext in ('completed','pending user')

DELETE hold_complete WHERE fkey = @.callid

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
insert hold_complete
select ins.AddedDT, ins.fkey, ins.actiontext,
ins.subactiontext, con.emailaddress, ca.loggeddt,
(con.forename + ' ' + con.surname) as contactname,
ca.summary, ca.notes,co.coordinator, co.coordinator,getdate(), ca.status,ca.lastsubaction,getdate(),ca.dateopened ,ca.companyname,getdate(),(null),ch.notes
FROM inserted as ins with (nolock)
join calls as ca with (nolock)on
ins.fkey = ca.callid
join contact as con with (nolock) on
ca.contactid = con.contactid
join company as co with (nolock) on
ca.companyid = co.companyid
join callshistory as ch with (nolock) on
ins.historyid = ch.historyid
where ins.actiontext = 'hold' and ins.Subactiontext in ('completed','pending user')|||I believe this error means Invalid Object Name, so the problem is probably one of your table names.

I'm not sure you can alias the "inserted" keyword. Try it without that.|||The syntax from SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED was used in my original trigger which allowed the duplicates so I know that bit works. I also tried the SP option you suggested and got no error message but no rows were deleted or inserted.

Can the inserted table be used outside of the original trigger for the SP ? When does it get deleted ?|||It's only around for the virtual instance the trigger is fired...once the trigger is done, it's gone...|||So should this work ? (BTW it don't.)

CREATE TRIGGER tr_DELETE_DUPES ON Hold_Complete
after insert AS

DECLARE @.callid varchar

SELECT @.callid = fkey FROM inserted

delete from hold_complete where addeddt <> (select max(addeddt)from hold_complete) and fkey = @.callid|||delete from hold_complete where addeddt NOT IN (select max(addeddt)from hold_complete) and fkey = @.callid|||I'm afraid that didn't delete the dupe rows either. Is there anyway that a message can be printed that will show the @.callid variable to make sure that's being captured properly|||You need to understand that the virtual tables will hold ALL of the affected rows...not just 1...your code will only grab the last one from the table...

you need to think set based...

And if you trying to DELETE dups AFTER the INSERT, I would think you would gewt a dup key error, and the trans would rollback and the trigger won't fire...

Assuming you have a pk contraint that is...

Also I would recommend removing the ISOLATION LEVEL code...

If you check @.@.ERROR in the sproc that is doing the INSERT you'll catch the error there...

You can then do an ipdate if you want to change the values...

I wouldn't do this in a trigger (Unless of course you don't have control over the DML, then I would)...|||CREATE TRIGGER tr_DELETE_DUPES ON Hold_Complete
after insert AS

DELETE
FROM hold_complete
INNER JOIN inserted
ON hold_complete.fkey = inserted.fkey
WHERE addeddt <> (select max(addeddt) from hold_complete)|||Brett

The hold_complete table is updated from an insert trigger on another table when certain criteria are met. This is unlikely to insert more than 1 row at a time. How would you suggest I go about deleting any previous entries with the same callid ?

bpd

It didn't like the join on a delete statement.|||My Bad:

DELETE hold_complete
FROM hold_complete
INNER JOIN inserted
ON hold_complete.fkey = inserted.fkey
WHERE addeddt <> (select max(addeddt) from hold_complete)|||Originally posted by Bracksboy
Brett

The hold_complete table is updated from an insert trigger on another table when certain criteria are met. This is unlikely to insert more than 1 row at a time. How would you suggest I go about deleting any previous entries with the same callid ?

bpd

It didn't like the join on a delete statement.

Cascading triggers?

Also it's not a matter of how likely an event can happen...it's whether it can or it can't...there are no colors here...it's either black or white...

It's not a matter of IF, it's a matter of WHEN

First, do you have a Primary Key contraint on CallId now?

If not, first find out what you're dealing with...

SELECT CallId, COUNT(*) FROM hold_complete GROUP BY CallId HAVING COUNT(*) > 1

If that returns nothing, you're in business...and just add the contraint, and you won't have to worry about the trigger at all...

If it does, then you need to sanatize the data, then add the contraint...|||Originally posted by Brett Kaiser

First, do you have a Primary Key contraint on CallId now?

If not, first find out what you're dealing with...

SELECT CallId, COUNT(*) FROM hold_complete GROUP BY CallId HAVING COUNT(*) > 1

If that returns nothing, you're in business...and just add the contraint, and you won't have to worry about the trigger at all...

If it does, then you need to sanatize the data, then add the contraint...

I don't have a constraint on callid and I don't think it will work with this. If I understand constraints correctly if I set one on callid it won't allow an insert where that callid already exists. Is that correct ?

The inserted data is not going to always have a unique callid so I just need to store the latest row of data and remove all previous rows with that callid. The rows that are inserted are tracking details from a call logging system that meet certain criteria and this can have multiple rows with the same callid. From this data I have a VB app that emails confirmation and then sets dates for chasers and automated closure. I need to delete the existing rows so only the latest info is used in the app.

It's all very new to me.|||You could always just do the delete first, then do an insert.|||but that's the whole point of a realtional database...

You should check to see if the row exists first...if it does grab it, and update it...

Then there's the problem if the row doesn't exist, you start enetering info, someone slides the key while you're typing and get it in...

You need to error handle and check @.@.Error to see if the execution was successful or not....

no MOO's about it...|||Thanks for your help guys but had a complete rethink and I have the application sorting the records I want using the following select statement in the ADO connection :

SELECT * FROM hold_complete a
WHERE exists (SELECT fkey FROM hold_complete b
GROUP BY fkey HAVING max(b.addeddt) = a.addeddt)
ORDER by fkey

Pass a parameter to a trigger?

The application developers came up with an interesting requirement that Id like some help with.

They want to be able to somehow pass a parameter to the trigger that is fired when you do something to a table. The parameter would control what logic is executed within the trigger.

The two solutions that I proposed, but were found unacceptable by them were:
1. Have parameter as a column within the table itself.
2. Dont use Update, Delete, Insert but instead use a stored procedure.

Thoughts are welcome.

Thanks.As an alternative to #1 you can have a dedicated parameter table that needs to acquire a control value before the action query is issued. But I would choose #2 any time over anything else. What was the argument your developers used to dismiss your #2?|||The bias against the stored procedure was that they are using a VB .NET code generator that creates the "behind the scenes" SQL statements based upon their screen construction. It's cumbersome (read that as extra work!) for them to model screens against stored procedures - especially if they had to write them themselves.|||I guess programmers become lazier and lazier. So they want a trigger that "takes" a parameter rather than having a stored procedure that does take a parameter? Would they like for the trigger to return resultset for them too? I bet they show up to work by 11:30AM and stay up all night playing Unreal Tournament too ;)|||I would also like to know whether there is a way to pass parameter to a trigger. Does anyone know how to?
Its not the question whether programmers are lazy or not. Sometimes they run into timelines issue or they may not be able to change the design of the application.|||no, no, no. programmers are lazy. Is that you Jawad?|||There is only one way I know to "pass a paramter" to a Trigger...but it's not really a parameter, but more a select from sysprocesses, which would be dangerous with these developes, so the sort answer is no you can't

Well as cumbersome as it is to read a sproc, what about the complexity of dynamically telling a trigger what to do?

You got a for instance?

Why not create them Views that contain the business logic they want to implement. AND restrict their access to those views.

You seriously are going to have a problem with the guys..

Can you imagine...developers telling a dba what to do...

What terrible times we are living in...|||A very simple and elegant solution is ... coming from the COBOL times ... Back then it used to be called a parameter file (of course that was an invention of lazy and not very bright COBOL developers that had no earthly idea about LINKAGE section). Now, we live in times of lazy SQL developers, but the brilliance of parameter file/table seems to shine bright ;)

pass a parameter to a trigger

Hello,

Is there a way to pass a parameter to a trigger?

Thanks,

To the best of my knowledge, no you can't. What were you hoping to do?