Clicky

Hi,

I have lookup tables in my database. The client wants to ensure that usres can not make any changes to the tables while they do the dataentry using ACCESS (adp) front end which is connected to sql 2005 database.

They do not want to use form validation however they asked for using triggers to validate and generate error message if anyone wants to delete or update a value but it should allow to insert new values.

Lets say  I have a lookup table AgeGroup with columns: ID, AgeGroup

tblAgeGroup
ID           AgeGroup
1            0-5
2            6-10

how can I right the trigger as well as display error message?

Thanks in advance

asked 11/01/2011 11:53

shmz's gravatar image

shmz ♦♦


26 Answers:
You can do something like this in your backend:

CREATE TRIGGER validate_AgeAgroup
ON AgeGroup
AFTER UPDATE, DELETE
AS RAISERROR ('Update & Delete not Allowed', 16, 10);
GO

Also, in your frontend you should have a try-catch statement to capture the messages from the SQL Database by the RAISERROR command.

Cheers!
link

answered

armchang's gravatar image

armchang

Thanks armchange.
How can I allow Insert new records (if not already exists)?

Any sample to sow how to get error message from sql in ACCESS?
link

answered 2011-11-01 at 19:53:02

shmz's gravatar image

shmz

Better create a separate INSERT trigger for that:

CREATE TRIGGER validate_insertAgeAgroup
ON AgeGroup
AFTER INSERT
AS
DECLARE @COUNT INTEGER;
SELECT @COUNT=COUNT(*) FROM tblAgeGroup WHERE AgeGroup in (SELECT AgeGroup FROM inserted);
IF (@COUNT > 0)
   RAISERROR ('Insert not Allowed', 16, 10);
GO

Note: The inserted is the changes you've saved from frontend.

MS Access is using Visual Basic for Applications thus you can learn try catch statements with this link:

http://msdn.microsoft.com/en-us/library/fk6t46tz(v=VS.80).aspx
link

answered 2011-11-01 at 20:26:15

armchang's gravatar image

armchang

Not sure if I follow this: SELECT AgeGroup FROM inserted
how can I save this from front end and what is inserted?
link

answered 2011-11-01 at 21:03:36

shmz's gravatar image

shmz

inserted is logical/conceptual table
the structure of table is similar to the table on which the trigger is defined, the table on which user action is attempted, this containts old/new values of the rows that may be modified by user action.
link

answered 2011-11-01 at 21:09:26

sachinpatil10d's gravatar image

sachinpatil10d

As sachinpatil10d said it, I want to explain that you don't need to change the code I've created for you as that one is the standard. (inserted is a temporary table used by SQL Server for getting the values you've done after you call INSERT statement from frontend)

link

answered 2011-11-01 at 23:03:42

armchang's gravatar image

armchang

Thanks armchang,

I tried your code and it worked fine. couple of questions:

when I set a trigger on a table for update or delete, this stops all records from being updated or deleted. If I want to make sure that all data up until today can not be deleted or updated but any data that is inserted from today onward is updatable or deletable, what should be done?

Also when I created insert trigger, the way it worked was: If record already existed then it did show the raiserror message but if record did not exist then it added the record however still shows raiserror message. here is my code:

CREATE TRIGGER validate_InsertEmployee
ON Table_Employee
AFTER INSERT
AS
Declare @count int;
Select @count = Count(*) FROM table_Employee
WHERE emp_number in (Select emp_num from inserted)

IF (@count > 0 )
RAISERROR ('Record is already existed.',16,10)
go




I also have tables with a lot of foreign key relationship. how trigger is affecting them?

Many Thanks
link

answered 2011-11-01 at 23:11:02

shmz's gravatar image

shmz

when I set a trigger on a table for update or delete, this stops all records from being updated or deleted. If I want to make sure that all data up until today can not be deleted or updated but any data that is inserted from today onward is updatable or deletable, what should be done?

answer: I would suggest that you'd add  DATE field so you'd filter only the DATE from today onwards to delete.

Also when I created insert trigger, the way it worked was: If record already existed then it did show the raiserror message but if record did not exist then it added the record however still shows raiserror message. here is my code:

answer: maybe use the FOR INSERT instead of AFTER INSERT as shown below:


CREATE TRIGGER validate_InsertEmployee
ON Table_Employee
FOR INSERT
AS
Declare @count int;
Select @count = Count(*) FROM table_Employee
WHERE emp_number in (Select emp_num from inserted)

IF (@count > 0 )
RAISERROR ('Record is already existed.',16,10)
go
link

answered 2011-11-02 at 19:17:17

armchang's gravatar image

armchang

RAISERROR just sends an error message.

To stop the modification you must also include a ROLLBACK.

The ROLLBACK will of course cancel everything in the transaction, not just the modification(s) to that one table.
link

answered 2011-11-03 at 04:20:40

ScottPletcher's gravatar image

ScottPletcher

ScottPletcher: We're not using any TRANSACTION.
link

answered 2011-11-03 at 11:02:48

armchang's gravatar image

armchang

Lol.  Yes you are:

*ALL* table modifications are done as part of a transaction in SQL Server.

If you just use RAISERROR and don't issue a ROLLBACK, the modifications will *not* be cancelled.
link

answered 2011-11-03 at 12:28:40

ScottPletcher's gravatar image

ScottPletcher

Thanks everyone,

Here is how I got them working:
For Update of child tables:


CREATE TRIGGER ValidateProductUpdate
ON Product
AFTER Update
AS

DECLARE @sdate datetime
SET @sdate  = (SELECT sdate FROM      deleted)

If (@sdate <= '2010-10-10')
BEGIN
RAISERROR ('Record can not be Updated.',16,10)
ROLLBACK TRANSACTION
RETURN
END

AND for Delete:

CREATE TRIGGER ValidateProductDelete
ON Product
INSTEAD OF DELETE
AS
DECLARE
  @sdate INT
, @supplier_Id      INT
, @bug_id            INT

SET @patient_ID = (      SELECT      TOP 1 patient_ID      FROM      deleted)                  
SET @bug_ID            = (      SELECT      TOP 1 PBug_AutoIDPK FROM      deleted)
                              
SET @period            = (      SELECT      TOP 1 p.Period
                              FROM      patient      p
                              JOIN      bug            b      ON      b.patient_ID = p.patient_ID
                              JOIN      deleted d      ON      d.patient_ID = p.patient_ID
                              WHERE      p.patient_Id = @patient_Id
                              )
                  
IF @period < = 41
BEGIN
      --RAISERROR('Record can not be deleted.',16,1)
      ROLLBACK
END

ELSE
BEGIN
      DELETE FROM Bug
      WHERE      patient_ID = @patient_ID
      AND            PBug_AutoIDPK = @bug_Id
      COMMIT
END
GO
link

answered 2011-11-03 at 12:41:10

shmz's gravatar image

shmz

ignore previous message, was't finished and submitted :(


Thanks everyone,

Here is how I got them working:
For Update of child tables:


CREATE TRIGGER ValidateProductUpdate
ON Product
AFTER Update
AS

DECLARE @sdate datetime
SET @sdate  = (SELECT sdate FROM      deleted)

If (@sdate <= '2010-10-10')
BEGIN
RAISERROR ('Record can not be Updated.',16,10)
ROLLBACK TRANSACTION
RETURN
END

AND for Delete:

CREATE TRIGGER ValidateProductDelete
ON Product
INSTEAD OF DELETE
AS
DECLARE
  @sdate INT
, @supplier_Id      INT
, @product_id            INT

SET @supplier_ID            = (      SELECT      TOP 1 supplier_ID      FROM      deleted)                  
SET @product_ID            = (      SELECT      TOP 1 product_ID      FROM      deleted)
                             
SET @sdate          = (      SELECT      TOP 1 p.sdate
                              FROM     supplier p
                              JOIN     product            b      ON      b.supplier_ID = p.supplier_ID
                              JOIN      deleted d      ON      d.supplier_ID = p.supplier_ID
                              WHERE      p.supplier_Id = @supplier_Id
                              )
                 
IF @sdate < = '2011-10-10'
BEGIN
      --RAISERROR('Record can not be deleted.',16,1)
      ROLLBACK
END

ELSE
BEGIN
      DELETE FROM product
      WHERE      supplier_ID = @supplier_ID
      AND           prduct_ID = @product_Id
      COMMIT
END
GO

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

Right now I need to do a cascade delete of parent table (supplier table)
I do not want to set on delete cascade on foreigh key of child table.

I wrote a trigger this way but it does not work. how can I get it work:

CREATE TRIGGER amr_ValidateSupplierDelete
ON Supplier
Instead Of DELETE
AS
DECLARE  @sdate datetime
SET @sdate = (SELECT sdate FROM deleted)

If (@sdate <= '2011-10-10')
BEGIN
--RAISERROR ('Record can not be deleted.',16,10)
ROLLBACK TRANSACTION
RETURN
END

ELSE
BEGIN

            DECLARE @Supplier_id      INT
            SET @Supplier_id = (SELECT Supplier_id FROM deleted)

            IF EXISTS (SELECT * FROM product WHERE Supplier_ID = @Supplier_id)
                  BEGIN
                  DELETE      FROM      product
                              FROM      product s      
                  INNER      JOIN              Supplier p      ON      s.Supplier_id      = p.Supplier_id
                  WHERE s.Supplier_ID = @Supplier_id
            END
                  
                  
            IF EXISTS (SELECT * FROM supplier WHERE supplier _ID = @supplier _id)
                  BEGIN                  
                  DELETE FROM supplier WHERE supplier _ID = @supplier _id
            END
            
            COMMIT
END

                  

the else section does not go through all queries and just does one at the time, like deletes the product but does not delete supplier....how can I get it work?

Thanks
link

answered 2011-11-03 at 16:05:01

shmz's gravatar image

shmz

ScottPletcher: Lol
link

answered 2011-11-03 at 16:16:06

armchang's gravatar image

armchang

shmz: You need to create CURSOR to loop the data inside the deleted table and get the supplier_ID.
e.g.:

DECLARE @Supplier_ID INT;
DECLARE Sup_Cursor CURSOR FOR SELECT  Supplier_id FROM deleted;
OPEN Sup_Cursor;
FETCH NEXT FROM Sup_Cursor INTO @Supplier_ID;

WHILE @@FETCH_STATUS = 0
BEGIN
      IF EXISTS (SELECT * FROM product WHERE Supplier_ID = @Supplier_id)
                  BEGIN
                  DELETE      FROM      product
                              FROM      products
                  INNER      JOIN              Supplier p      ON      s.Supplier_id      = p.Supplier_id
                  WHERE s.Supplier_ID = @Supplier_id
            END
                 
                 
            IF EXISTS (SELECT * FROM supplier WHERE supplier _ID = @supplier _id)
                  BEGIN                  
                  DELETE FROM supplier WHERE supplier _ID = @supplier _id
            END
     FETCH NEXT FROM Sup_Cursor INTO @Supplier_ID;
END
CLOSE Sup_Cursor;
DEALLOCATE Sup_Cursor;
link

answered 2011-11-03 at 18:50:37

armchang's gravatar image

armchang

>> ScottPletcher: Lol <<

??  Are you laughing at my assertion that table mods are always part of a transaction?  If so, you are unfortunately mistaken.  Transactions are fundamental to the way relational dbs function.

The thing is, even if you don't explicitly start a transaction with BEGIN TRANSACTION, SQL automatically starts one itself ** because all table mods must be part of a transaction in SQL Server ** (and other relational dbs).

link

answered 2011-11-03 at 19:03:48

ScottPletcher's gravatar image

ScottPletcher

??  Are you laughing at my assertion that table mods are always part of a transaction?  If so, you are unfortunately mistaken.  Transactions are fundamental to the way relational dbs function.

The thing is, even if you don't explicitly start a transaction with BEGIN TRANSACTION, SQL automatically starts one itself ** because all table mods must be part of a transaction in SQL Server ** (and other relational dbs).

No, I'm laughing at my answer. It's me and not you.
link

answered 2011-11-03 at 20:56:58

armchang's gravatar image

armchang

armchang,

te problem is not looping through the records:

If I have 3 products A, B , C related to supplier 112

this code:
F EXISTS (SELECT * FROM product WHERE Supplier_ID = @Supplier_id)
                  BEGIN
                  DELETE      FROM      product
                              FROM      products
                  INNER      JOIN              Supplier p      ON      s.Supplier_id      = p.Supplier_id
                  WHERE s.Supplier_ID = @Supplier_id
            END
                 
deletes product A,B,C but does ot get to
                 
            IF EXISTS (SELECT * FROM supplier WHERE supplier _ID = @supplier _id)
                  BEGIN                  
                  DELETE FROM supplier WHERE supplier _ID = @supplier _id
            END
and suplier 111 does not get deleted.
In my real query,  I have more tables have relationship with supplier table and only the first set of (If Exists,....) is getting executed...
link

answered 2011-11-03 at 22:46:18

shmz's gravatar image

shmz

I am not sure if begin transctio,commit,...is not declared correctly?...
link

answered 2011-11-03 at 23:01:56

shmz's gravatar image

shmz

to clarify this:

In my application a single supplier can be deleted. when I try to delete supplier 111, first I should go a and delete all information related to supplier 111 in all child tables, one of the child tables here is product.

my trigger allow to delete all related products but then does not delete the supplier, if I have more child tables in my query(more if exists,....) only the first set of if exists gets executed and the rest ignored....
link

answered 2011-11-03 at 23:03:02

shmz's gravatar image

shmz

You can remove the  IF EXISTS (SELECT * FROM supplier WHERE supplier _ID = @supplier _id) line already and use only the Delete line:

Change this line:>>>

IF EXISTS (SELECT * FROM supplier WHERE supplier _ID = @supplier _id)
                  BEGIN                  
                  DELETE FROM supplier WHERE supplier _ID = @supplier _id
            END

To:>>>>
DELETE FROM supplier WHERE supplier _ID = @supplier _id

Then you will get your answer.

link

answered 2011-11-03 at 23:27:14

armchang's gravatar image

armchang

You don't need a cursor at all.  That will just slow you down dramatically.
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
DELETE FROM p
FROM product p
INNER JOIN deleted d ON
    d.supplier_id = p.supplier_id

DELETE FROM oc
FROM other_child_table oc
INNER JOIN deleted d ON
    d.supplier_id = oc.supplier_id

DELETE FROM s
FROM supplier s
INNER JOIN deleted d ON
    d.supplier_id = s.supplier_id
link

answered 2011-11-03 at 23:28:38

ScottPletcher's gravatar image

ScottPletcher

>>my trigger allow to delete all related products but then does not delete the supplier, if I have more child tables in my query(more if exists,....) only the first set of if exists gets executed and the rest ignored.... <<
And that is probably because of the way you have written the TRIGGER code.  Your code only supports UPDATE and DELETE that affect only one row.
link

answered 2011-11-04 at 00:27:14

acperkins's gravatar image

acperkins

acperkin,

You are very right. I am having problem with the code now. How should the trigger be modified to affect multiple rows?
Thanks in advance
link

answered 2011-11-05 at 16:26:24

shmz's gravatar image

shmz

here are sample of actual trigger I have:


CREATE TRIGGER Validate_P_Delete
ON P
INSTEAD OF DELETE
AS            
IF EXISTS ((SELECT period FROM deleted) <= 4)
BEGIN
                  RAISERROR ('This record can not be deleted.',16,10)
                  ROLLBACK TRANSACTION
                  RETURN
END
ELSE
BEGIN
                              
                  DELETE      FROM      condition      
                              FROM      condition            c      
                              JOIN      deleted                  d      ON      d.p_id      = c.p_id
                  
                        
                  DELETE      FROM      bXX      
                              FROM      bXX                              
                              JOIN      deleted                  d      ON      d.p_id      = bXX.p_id             
                  
                  DELETE FROM            p                  
                              FROM      p                  
                              JOIN      deleted                  d      ON      d.p_id      = p.p_id
                              WHERE      p.p_ID      = (SELECT TOP 1 p_id FROM deleted)       
END
GO                  

--*************************************
-- P - Update
--*************************************

CREATE TRIGGER Validate_P_Update
ON P
AFTER Update
AS
IF EXISTS ((SELECT period FROM Inserted) < 5)
BEGIN
      RAISERROR ('Record can not be updated.',16,10)
      ROLLBACK TRANSACTION
      RETURN
END
GO

--*************************************
-- P- Insert
--*************************************

CREATE TRIGGER Validate_P_Insert
ON P
AFTER INSERT
AS
IF EXISTS ((SELECT period FROM Inserted) < 5)
BEGIN
      RAISERROR ('Record can not be inserted.',16,10)
      ROLLBACK TRANSACTION
      RETURN
END
GO


--*************************************
-- bXX - Delete
--*************************************

CREATE TRIGGER Validate_bXX_Delete
ON bXX
INSTEAD OF DELETE
AS
DECLARE  @period INT
SET @period            = (      SELECT      TOP 1 p.Period
                              FROM      p
                              JOIN      deleted d      ON      d.p_ID = p.p_ID
                              WHERE      p.p_Id = (SELECT TOP 1 p_id FROM deleted))                  
IF @period <5
BEGIN
      RAISERROR('This record can not be deleted.',16,1)
      ROLLBACK
END
ELSE
BEGIN
      DELETE      FROM bXX
                  FROM bXX b
                  JOIN deleted d ON b.PBxx_AutoID = d.PBxx_AutoID
END
GO

--*************************************
-- bXX - Update
--*************************************

CREATE TRIGGER Validate_bxx_Update
ON bXX
AFTER UPDATE
AS
DECLARE  @period       INT                  
SET @period            = (      SELECT      TOP 1 p.Period
                              FROM            p
                              JOIN      bug            b      ON      b.p_ID = p.p_ID
                              WHERE      p.p_Id = (SELECT      TOP 1 p_ID FROM inserted) )
IF @period < 5
BEGIN
      RAISERROR('This record can not be updated.',16,1)
      ROLLBACK TRANSACTION
      RETURN
END
GO

--*************************************
-- bXX - Insert
--*************************************

CREATE TRIGGER Validate_bXX_Insert
ON bXX
AFTER INSERT
AS
DECLARE  @period      INT                  
SET @period            = (      SELECT      TOP 1 p.Period
                              FROM              p
                              JOIN      bug            b      ON      b.p_ID = p.p_ID
                              WHERE      p.p_Id = (SELECT      TOP 1 p_ID FROM inserted) )
IF @period < 5
BEGIN
      RAISERROR('This record can not be inserted.',16,1)
      ROLLBACK TRANSACTION
      RETURN
END
GO
link

answered 2011-12-10 at 08:20:57

shmz's gravatar image

shmz

thanks
link

answered 2011-12-10 at 08:36:01

shmz's gravatar image

shmz

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×132
×1

Asked: 11/01/2011 11:53

Seen: 258 times

Last updated: 12/14/2011 07:49