• 0

MSSql Triggers


Question

Hi,

I'm trying to learn triggers in mssql and have a problem with two that I'm working on.

Both are using the table "Orders"

1. Not allowing a batch insert or update statement to be run.

2. If a user enters or updates the ShippedDate field, and provides a value that is before the OrderDate field, it will be blocked (rolled back), and prints a warning message.

Any help would be greatly appreciated... been working on this for a few hours now :(.

---------------------------------------------------------------------------------------------------------------------------------------------------

UPDATE:

I got #2... just don't know how to block a batch insert :(....

CREATE TRIGGER checkDate ON dbo.Orders

AFTER INSERT, UPDATE

AS

declare @sd datetime

declare @od datetime

SELECT @sd = (select ShippedDate from inserted)

SELECT @od = (select OrderDate from inserted)

IF (@sd < @od)

BEGIN

ROLLBACK TRAN

raiserror ('Please choose a shipped date that is after the order date', 16, 1)

END

Was my answer for #2... I assume this is right.

Link to comment
Share on other sites

4 answers to this question

Recommended Posts

  • 0

You may want to post your findings for both of your questions, in case someone else has a similar problem later :)

Link to comment
Share on other sites

  • 0
You may want to post your findings for both of your questions, in case someone else has a similar problem later :)

My answer was:

CREATE TRIGGER checkDate ON dbo.Orders

AFTER INSERT, UPDATE

AS

declare @count int

SELECT @count = (select count(*) from inserted)

IF (@count > 1)

BEGIN

ROLLBACK TRAN

raiserror ('No batch inserts/updates allowed', 16, 1)

END

ELSE

declare @sd datetime

declare @od datetime

SELECT @sd = (select ShippedDate from inserted)

SELECT @od = (select OrderDate from inserted)

IF (@sd < @od)

BEGIN

ROLLBACK TRAN

raiserror ('Please choose a shipped date that is after the order date', 16, 1)

END

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.