Solved

SQL Server Trigger

Posted on 2010-09-07
20
343 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Error Log - logging period 1 32
How to Install SSIS without the SQL Server CD 3 42
Excel conversion issue with Sql server 14 45
TSQL previous 5 22
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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

930 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

14 Experts available now in Live!

Get 1:1 Help Now