Link to home
Start Free TrialLog in
Avatar of dodgerfan
dodgerfanFlag for United States of America

asked on

SQL Server Trigger

Is there an example somewhere of a conditional trigger? I only want to fire a trigger that inserts a new record into a history table if the value froim another table is less than or equal to a set value. I;m not sure how to do this.
Avatar of xiong8086
xiong8086

1. you set your trigger for 'INSERT' only,

2. compare the set value with the inserted.value, if condition is what you want, then execute the statement you want.

Create Trigger .... For INSERT
AS
If inserted.comparingColumn = 'certain set value'
   BEGIN
     -- your statement
   END
else
   BEGIN
     -- other statements
   END

For more accurate syntax, please refer to SQL server help documentation.

Hope it helps.
you cannot use inserted.comparingColumn like that since inserted is a table not row...

you can use

select @insvalue = min(someColumn) from inserted;
select @setValue = someColumn from setTable where id='trgSet';

if @insvalue > @setValue
begin
  ...
end;
Avatar of dodgerfan

ASKER

Here's what I ahve so far. The trigger compiled but it does not perforn the way I want it to. So if @Available is 0, then show the error message and don;t allow the change to the record and roll back the update . If it is greater than 0, then allow update and insert a record into the history table.

CREATE TRIGGER member_max
ON Rental
FOR INSERT, UPDATE
AS
DECLARE @rental_limit int,
            @current_rentals int,
            @available int
SELECT  @rental_limit = MembershipLimitPerMonth,
            @current_rentals = CurrentRentals,
            @available = Available
FROM vwMemberShipLimit
IF (@available = 0)
BEGIN
   RAISERROR ('Member cannot rent any more DVDs at this time.', 16, 1)
   ROLLBACK TRANSACTION
END
ELSE
IF (@available > 0)
BEGIN
      INSERT RentalHistory (RentalId, MemberId, DVDId, RentalRequestDate, RentalShippedDate, RentalReturnedDate)
      SELECT RentalId, MemberId, DVDId, RentalRequestDate, RentalShippedDate, RentalReturnedDate
      FROM inserted
END
you can try this example

CREATE TRIGGER ExampleTrigger
ON TempTable
FOR INSERT
AS
DECLARE @Amtlimit int,
            @currentAmt int,
            @available int
SELECT  @Amtlimit = LimitAmt,
            @currentAmt = CurrentValue,
            @available = Available
FROM TempTable
IF (@available = 0)
BEGIN
   RAISERROR ('Member cannot rent any more DVDs at this time.', 16, 1)
   ROLLBACK TRANSACTION
END
ELSE
IF (@available > 0)
BEGIN
      INSERT TempTableHistory (Val1, Val2, Val3,...)
      SELECT Val1, Val2, Val3,...
      FROM inserted
END
I think for your objective, it may be easier for you to implement it in the insert store procedures, instead of trigger, as in the trigger, you cannot rollback the orginal transaction.

Just rewrite the insert or update stored procedure, and put all this conditions into the consideration
like

CREATE PROC sp_insert
@dd varchar()
as
BEGIN
   BEGIN Transaction

   if  
     BEGIN
      ...
      rollback transaction
      END
      else
      begin
        ...
       COMMIT Transaction
      end
END

dodgerfan, 33623956 seems ok to me, whats the issue here? what is not working?
The trigger I have now (33623956 ) compiles fine, but does not actually insert data or produce a message. I need the trigger to fire when a field gets updated in the rental table. The available variable comes from a view I created. So I'm trying to use a value from a view to determine if the trigger should be fired, which will prevent the update to the record and show a message. I do not need to insert a record into another table, as I first thought I would do. Seems to be a bit much. So I just need it to fire if the available value comes back 0.
i created this trigger and when i insert i get this


CREATE TRIGGER trg_Order
ON Orders
FOR INSERT
AS
DECLARE @available int = 0
IF (@available = 0) 
BEGIN
   RAISERROR ('Member cannot rent any more DVDs at this time.', 16, 1)
   ROLLBACK TRANSACTION
END

Open in new window

trigger.gif
HainKurt:

I tried your latest code. When I tried to compile I got this error: Line 0: Cannot assign a local variable. Line 6: Must declare the scalar variable "@available". Mine looks exactly like yours except the table name. How does it know where the available value is coming from. That value is in a different table (or view)? Thanks for the help.
can you please post the code you have right now
This is latest I have:
create trigger member_max
ON Rental
FOR INSERT
AS
DECLARE @available int = 0
IF(@available = 0)
BEGIN
RAISERROR('Member cannot rent any more DVDs at this time.', 16, 1)
ROLLBACK TRANSACTION
END
looks ok to me ;) what tool are you using? i am using SSMS, query editor to compile it...
i dont select anything, just paste this code into a new qry editor and hit F5 to compile...
All I have is SQL Server Management Studio (SQL Server 2005).
i tried your code and it works fine on rental table (i created it same as yours)
it compiled successfully, and when I try to insert i get message
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Alright, I changed one line and added another:
Declare @available int
Select @available = 0

Now it compiles and it fires. The problem is that it fires on every insert. I want it to fire only when Available = 0. And that Available value is stored in a different view. IS there some way to retrieve for that particular memberid? MemberId is part of the Rental recordset and the view that stores Available. Thansk again fro the help.
Good grief, I think I got it. Testing seems to have worked. The final trigger:

create trigger trg_rental
on Rental
For Insert
As
Begin
  Declare @available int
  Declare @memberid int

  Select @memberid = (select MemberId from inserted)
  Select @available = (select available from vwAvailableRentals where MemberId = @memberid)

  If(@available <=0)
    Begin
        RAISERROR('This DVD is not available.', 16, 1)
        ROLLBACK TRANSACTION
    End
End

This seems to be working. I appreciate the help. I'm learning. I have not had to work with triggers or stored procedures in years, and even then it was pretty trivial stuff. I'm learning.
Thanks for the help and patience.
this will give you error

Select @memberid = (select MemberId from inserted)

if you bulk insert...

to use the trigger for one inserts check the count of inserted records...

select @count = count(1) from inserted
if @count ! = 1 exit

i am not sure about exit ;) but you got the idea...
Avatar of Anthony Perkins
>>Alright, I changed one line and added another:<<
That is correct you cannot use the following syntax in SQL Server 2005 (only in 2008):
DECLARE @available int = 0

And that is why it is really important to only include the relevant zones.  In your case SQL Server 2008 does not apply.