Prevent Insert If Condition Is Met

Prevent insert if condition is met

Your basic approach is sound. The trigger is a valid solution. It should work except for 3 problems:

1) Your naming convention:

We would need to see your exact table definition to be sure, but the evidence is there. The error message says: has no field "idanswer" - lower case. Doesn't say "idAnswer" - CaMeL case. If you create CaMeL case identifiers in Postgres, you are bound to double-quote them everywhere for the rest of their life.

• Are PostgreSQL column names case-sensitive?

2) Abort violating insert

• Either raise an EXCEPTION instead of a friendly NOTICE to actually abort the whole transaction.

• Or RETURN NULL instead of RETURN NEW to just abort the inserted row silently without raising an exception and without rolling anything back.

I would do the first. This will probably fix the error at hand and work:

CREATE FUNCTION trg_answer_insbef_check()
  RETURNS trigger AS
$func$
BEGIN
   IF (SELECT c.date FROM "Content" c WHERE c.id = NEW."idAnswer")
    < (SELECT c.date FROM "Content" c WHERE c.id = NEW."idQuestion") THEN
      RAISE EXCEPTION 'This Answer is an invalid date';
   END IF;
   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

The proper solution is to use legal, lower case names exclusively and avoid such problems altogether. That includes your unfortunate table names as well as the column name date, which is a reserved word in standard SQL and should not be used as identifier - even if Postgres allows it.

3) Should be a BEFORE trigger

CREATE TRIGGER insbef_check
BEFORE INSERT ON "Answer"
FOR EACH ROW EXECUTE PROCEDURE trg_answer_insbef_check();

You want to abort invalid inserts before you do anything else.

Of course you will have to make sure that the timestamps table Content cannot be changed or you need more triggers to make sure your conditions are met.

The same goes for the fk columns in Answer.

How to prevent insert when condition is met

Sounds like you just need a CHECK constraint

ALTER TABLE table1
  ADD CONSTRAINT CHK_Status
    CHECK(Status >= 4 OR TimeStamp IS NULL);

---

If you really, really wanted to use triggers for this (not recommended) then it should look like this

CREATE OR ALTER TRIGGER dbo.Prevent_Insert4
ON dbo.table1
FOR INSERT, UPDATE
AS

SET NOCOUNT ON;

IF  EXISTS(SELECT 1
    FROM inserted i
    WHERE i.StatusID < 4
      AND i.TimeStamp IS NOT NULL)
BEGIN
    THROW 50001, 'You can not set TimeStamp when StatusId value < 4', 1;
    -- no need for rollback, will happen automatically if you use THROW
END;

go

Creating trigger to prevent insert with condition from other table

try this

drop trigger if exists buku_dalam_pinjam_BI;
delimiter $$
CREATE  TRIGGER `buku_dalam_pinjam_BI`
BEFORE INSERT ON buku_dalam_pinjam
FOR EACH ROW BEGIN
    if exists (select 1 from anggota_dosen 
            where new.id_agt_dosen = anggota_dosen.id_agt_dosen and 
        anggota_dosen.ttl_proses_pinjam >=5) then
    signal sqlstate '45000' set message_text="error message";

    end if;
END $$
delimiter ;

MySQL Trigger to prevent INSERT under certain conditions

Based on this I'm not sure if it's possible to do it that way.

There's no support in MySQL's current
implementation of triggers to
voluntarily throw an exception and
abort the statement that spawned the
trigger.

The workaround I've found is to write
a BEFORE trigger to set one of the
not-NULL columns in the table to NULL,
thus violating its NOT NULL
constraint. This causes the statement
that spawned the trigger to be
aborted.

MySQL test before insert and cancel if condition is met

You have to use a signal

SIGNAL is the way to “return” an error. SIGNAL provides error
information to a handler, to an outer portion of the application, or
to the client. Also, it provides control over the error's
characteristics (error number, SQLSTATE value, message).

Thus you can have

CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    ->     IF NEW.amount < 0 THEN
    ->         SET NEW.amount = 0;
    ->     ELSEIF NEW.amount > 100 THEN
    ->         SET NEW.amount = 100;
           ELSE
               SIGNAL SQLSTATE '01000'
                 SET MESSAGE_TEXT = 'Sorry cannot insert', MYSQL_ERRNO = 1000;
    ->     END IF;
    -> END;

Update: You might also want to take a look at the error messages list and choose a more appropriate error number.

prevent insertion if regular expression is not met

The most recent versions of MySQL support check constraints:

alter table person add constraint ck_person_name
    check (name not regexp '[^A-Za-z]');

Then in most versions still in use, you can emulate it by creating a view:

create view v_person as
     select p.*
     from person p
     where p.name not regexp '[^A-Za-z]';

Then insert into the view. If the view logic filters out the insert, then the insert fails.

How to cancel an INSERT operation in a PostgreSQL Database from within a BEFORE-INSERT TRIGGER (Have done this in MySQL)

So As pointed in the link by @user2864740 , the Idea was good same as MySQL raising an exception but I was wrong in the spelling the code must be

RAISE EXCEPTION 'Please choose another number!!';

instead of raise debug in the TRIGGER body function

---

Related Topics