• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

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.
0
dodgerfan
Asked:
dodgerfan
  • 8
  • 8
  • 2
  • +2
1 Solution
 
xiong8086Commented:
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.
0
 
HainKurtSr. System AnalystCommented:
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;
0
 
dodgerfanAuthor Commented:
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Priya PerumpilavilCommented:
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
0
 
xiong8086Commented:
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

0
 
HainKurtSr. System AnalystCommented:
dodgerfan, 33623956 seems ok to me, whats the issue here? what is not working?
0
 
dodgerfanAuthor Commented:
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.
0
 
HainKurtSr. System AnalystCommented:
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
0
 
dodgerfanAuthor Commented:
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.
0
 
HainKurtSr. System AnalystCommented:
can you please post the code you have right now
0
 
dodgerfanAuthor Commented:
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
0
 
HainKurtSr. System AnalystCommented:
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...
0
 
dodgerfanAuthor Commented:
All I have is SQL Server Management Studio (SQL Server 2005).
0
 
HainKurtSr. System AnalystCommented:
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
0
 
HainKurtSr. System AnalystCommented:
even this is working fine (it is checking the min dvdid availibilty ;), it should check all inserted dvd's here, but this is just a sample)

when i insert into rental I get no message if availible > 0 and it decrements it by 1
if it is 0 i get message
create trigger trg_Rental
ON Rental
FOR INSERT
AS
BEGIN
	DECLARE @available int = 0
	DECLARE @dvdid int = 0

	select @dvdid = (select min(dvdid) from inserted)
	select @available = available from dvd where dvdid=@dvdid

	IF(@available = 0)
	BEGIN
	  RAISERROR('This DVD is not available.', 16, 1)
	  ROLLBACK TRANSACTION
	END
	ELSE
	update DVD set available = available-1 where dvdid=@dvdid;
END;

CREATE TABLE [dbo].[DVD](
	[DVDID] [int] NOT NULL,
	[Title] [varchar](50) NULL,
	[Year] [int] NULL,
	[Duration] [int] NULL,
	[Available] [int] NULL,
 CONSTRAINT [PK_DVD] PRIMARY KEY CLUSTERED 
(
	[DVDID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Rental](
	[RentalID] [int] NOT NULL,
	[MemberID] [int] NULL,
	[DVDID] [int] NULL,
	[RentalRequestDate] [date] NULL,
	[RentalShippedDate] [date] NULL,
	[RentalReturnedDate] [date] NULL,
 CONSTRAINT [PK_rental] PRIMARY KEY CLUSTERED 
(
	[RentalID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Open in new window

0
 
dodgerfanAuthor Commented:
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.
0
 
dodgerfanAuthor Commented:
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.
0
 
dodgerfanAuthor Commented:
Thanks for the help and patience.
0
 
HainKurtSr. System AnalystCommented:
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...
0
 
Anthony PerkinsCommented:
>>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.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 8
  • 8
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now