Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jillsy
Starting Member
20 Posts |
 Posted - 2006-01-24 : 10:33:24
|
| Hello, is it possible to pass a variable into a trigger and update the trigger by doing so? I tried the trigger below but i get an error message saying '@current_trigger_item must be declared'. I was hoping to use @current_trigger_item to hold an item passed to it(like a regular procedure) by updating the trigger when i wanted to change the variable. Hope that make sense. CREATE TRIGGER low_item (@current_trigger_item VARCHAR(15))on stock FOR INSERT AS BEGIN declare @price money declare @item_id int declare @item varchar(15) select @price = price from inserted select @item_id = item_id from inserted select @item = item from inserted If ( @price < (select min(price) from stock where ((item_id <>@item_id) AND (@item = @current_trigger_item) ) ) ) begin print 'Lowest priced item to date' end ENDJill |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-01-24 : 10:37:49
|
| What do u need to do?Y r u trying to use triggers to pass parameters? and to get a result as "print ... " ?This type of thing can be acieved by stored procedures or functions. |
 |
|
|
Jillsy
Starting Member
20 Posts |
Posted - 2006-01-24 : 11:08:51
|
| I'm trying to change the condition of the trigger by passing a variable to it (in this case current_trigger_item). Lets say initially i wanted the trigger to fire when the inserted item was 'pears', then at some later date i wanted the same trigger to fire when the inserted item was 'apples' i would need to update the trigger. So i need to pass 'apples', 'pears' or whatever to the trigger when updating it. Does this make things clearer?Jill |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-01-24 : 11:31:32
|
| This looks like something that should be in a stored procedure, not a trigger. You should just add the logic to the stored procedrue that does the insert.Also, your trigger will fail when more than on row is inserted, because you are inserting the values into a local variable. Triggers should always be written to allow inserts, updates, or deletes of more than one row.CODO ERGO SUM |
|
|
|
Jillsy
Starting Member
20 Posts |
Posted - 2006-01-24 : 11:50:00
|
| I know i can run a procedure to find the best price for a given item but i want to find a way to update a trigger by passing a new value to it. A trigger is pretty much a procedure and you can pass new values to those, so why not a trigger?By the way, how does one write triggers to allow inserts, updates, or deletes of more than one row?Yours amateurishlyJill |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-01-24 : 11:51:10
|
| U better refer the BOL for the use of Triggers and Stored Procedures |
|
|
|
|
|
|
|
|
Update trigger parameter