Solved

SQL Server Trigger

Posted on 2010-09-07
20
342 Views
Last Modified: 2012-06-27
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
Comment
Question by:dodgerfan
  • 8
  • 8
  • 2
  • +2
20 Comments
 
LVL 3

Expert Comment

by:xiong8086
ID: 33623516
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 33623544
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
 

Author Comment

by:dodgerfan
ID: 33623956
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
 
LVL 5

Expert Comment

by:Priya Perumpilavil
ID: 33624196
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
 
LVL 3

Expert Comment

by:xiong8086
ID: 33624701
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 33626365
dodgerfan, 33623956 seems ok to me, whats the issue here? what is not working?
0
 

Author Comment

by:dodgerfan
ID: 33628318
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 33629200
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
 

Author Comment

by:dodgerfan
ID: 33629364
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 33629457
can you please post the code you have right now
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:dodgerfan
ID: 33629599
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 33629785
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
 

Author Comment

by:dodgerfan
ID: 33629856
All I have is SQL Server Management Studio (SQL Server 2005).
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33629891
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
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 33629964
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
 

Author Comment

by:dodgerfan
ID: 33630053
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
 

Author Comment

by:dodgerfan
ID: 33630363
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
 

Author Closing Comment

by:dodgerfan
ID: 33630371
Thanks for the help and patience.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33630468
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33632765
>>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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now