can a trigger have parameters
-
Hi . I am designing a query . I want to write a trigger that accepts parameters. Is this possible how? and if not what should I do to solve this ? Thanks
The subject and the question are completely unrelated to each other.
yousefshokati wrote:
can a trigger have parameters?
NO.
yousefshokati wrote:
I am designing a query . I want to write a query that accepts parameters. Is this possible how?
and if not what should I do to solve thisWrite a Stored Procedure.
-
Hi . I am designing a query . I want to write a trigger that accepts parameters. Is this possible how? and if not what should I do to solve this ? Thanks
In SQL Server the trigger has two tables "inserted" and "deleted". These contain all columns in the table effected. Note for a delete query inserted is empty, for an insert deleted is empty.
-
In SQL Server the trigger has two tables "inserted" and "deleted". These contain all columns in the table effected. Note for a delete query inserted is empty, for an insert deleted is empty.
thanks for your attention . I know that we have those tables in triggers . I am not doing dml action to effect them . I have a query that returns some "Personnel Codes" that these codes should be in the trigger as input . Now I am looking a way to solve this problem . thanks
-
thanks for your attention . I know that we have those tables in triggers . I am not doing dml action to effect them . I have a query that returns some "Personnel Codes" that these codes should be in the trigger as input . Now I am looking a way to solve this problem . thanks
It is difficult to decipher what you really mean but let me take a a guess. You have a query that returns Personnel codes... I am assuming that you want to do something with personnel codes. If you are to use a trigger, then you must be peforming inserts, updates or deletes in a table. You said you're not doing DML, so triggers don't apply! You don't describe the problem in sufficient detail but I'll make a guess. You're getting some values using a query and you want to do something based on the results returned. I think you need to do everything in a stored procedure. Your stored procedure would have a section that queries for personnel codes. It would also have a section that performs some action based on the personnel codes. If you would describe the problem in sufficient detail, it would be easier to help. I would also suggest tha you avoid describing the problem in terms of the solution i.e. your problem description presupposes that triggers are the answer. Just state the problem in sufficient detail.
-
It is difficult to decipher what you really mean but let me take a a guess. You have a query that returns Personnel codes... I am assuming that you want to do something with personnel codes. If you are to use a trigger, then you must be peforming inserts, updates or deletes in a table. You said you're not doing DML, so triggers don't apply! You don't describe the problem in sufficient detail but I'll make a guess. You're getting some values using a query and you want to do something based on the results returned. I think you need to do everything in a stored procedure. Your stored procedure would have a section that queries for personnel codes. It would also have a section that performs some action based on the personnel codes. If you would describe the problem in sufficient detail, it would be easier to help. I would also suggest tha you avoid describing the problem in terms of the solution i.e. your problem description presupposes that triggers are the answer. Just state the problem in sufficient detail.
Hi . thanks for your attention . The problem is I have list of personnels that every month give some money to the bank of our firm . If their balance reaches to for example 100$ then they can enter to the list of people who can use loan (TurnsTable). Now I have a SP that each month adds for example 20$ to all of our staff's Balance all in the same time . this is an important insert to my DB because when an insert takes place I want to write a trigger to check the total balance after insert.If the balance would reach to the special amount(100$) then this person's Personnel code must be inserted to the TurnsTable which maintains the list of personnel who wants loan . because of this i needed to know that can i send parameters to the triggers ? what do you suggest to my problem ? How can i solve it?
-
Hi . thanks for your attention . The problem is I have list of personnels that every month give some money to the bank of our firm . If their balance reaches to for example 100$ then they can enter to the list of people who can use loan (TurnsTable). Now I have a SP that each month adds for example 20$ to all of our staff's Balance all in the same time . this is an important insert to my DB because when an insert takes place I want to write a trigger to check the total balance after insert.If the balance would reach to the special amount(100$) then this person's Personnel code must be inserted to the TurnsTable which maintains the list of personnel who wants loan . because of this i needed to know that can i send parameters to the triggers ? what do you suggest to my problem ? How can i solve it?
This is not a good idea for a trigger. You could create an AFTER INSERT trigger that calculates a new balance and if that balance exceeds $100, to then insert a record into another table. You now have the potential for creating a circular operation which is not good. The previous suggestions for using a procedure to perform the balance calculation and to then insert or delete based upon the result of that calculation makes much more sense and will be prone to less errors. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
Hi . thanks for your attention . The problem is I have list of personnels that every month give some money to the bank of our firm . If their balance reaches to for example 100$ then they can enter to the list of people who can use loan (TurnsTable). Now I have a SP that each month adds for example 20$ to all of our staff's Balance all in the same time . this is an important insert to my DB because when an insert takes place I want to write a trigger to check the total balance after insert.If the balance would reach to the special amount(100$) then this person's Personnel code must be inserted to the TurnsTable which maintains the list of personnel who wants loan . because of this i needed to know that can i send parameters to the triggers ? what do you suggest to my problem ? How can i solve it?
I would create a stored procedure that takes two parameters: personnel code and the amount by which to update the personnel balance. I would also use a transaction to ensure that no two users update the same record at the same time
CREATE PROCEDURE updateBalace
@personnelCode int--could be other datatype
@amount decimal--could be real, money or whatever
ASSET @actionAmount decimal=100.00
BEGIN TRAN
UPDATE Personnel SET Balance=Balance+Amount WHERE PersonnelCode=@personnelCodeIF EXISTS(SELECT Balance FROM Personnel WHERE Balance>@actionAmount AND
PersonnelCode=@personnelCode)--Check if updated record exceeds certain balance
BEGIN
--Insert record into turnsTable and play some funky music :)END
--check for errors
IF @@ERROR >0
ROLLBACK TRAN
ELSE
COMMIT TRANMy code is not kosher but you get the general idea.
-
I would create a stored procedure that takes two parameters: personnel code and the amount by which to update the personnel balance. I would also use a transaction to ensure that no two users update the same record at the same time
CREATE PROCEDURE updateBalace
@personnelCode int--could be other datatype
@amount decimal--could be real, money or whatever
ASSET @actionAmount decimal=100.00
BEGIN TRAN
UPDATE Personnel SET Balance=Balance+Amount WHERE PersonnelCode=@personnelCodeIF EXISTS(SELECT Balance FROM Personnel WHERE Balance>@actionAmount AND
PersonnelCode=@personnelCode)--Check if updated record exceeds certain balance
BEGIN
--Insert record into turnsTable and play some funky music :)END
--check for errors
IF @@ERROR >0
ROLLBACK TRAN
ELSE
COMMIT TRANMy code is not kosher but you get the general idea.
SilimSayo wrote:
--Insert record into turnsTable and play some funky music :)
:laugh: :laugh: :laugh:
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
SilimSayo wrote:
--Insert record into turnsTable and play some funky music :)
:laugh: :laugh: :laugh:
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
Hi . I am designing a query . I want to write a trigger that accepts parameters. Is this possible how? and if not what should I do to solve this ? Thanks
THE TRIGGER WON'T HAVE PARAMETERS.