Create Trigger Prevent Insert

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.

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 ;

Prevent Insert Trigger

"it was a class question."

I had a chat with a university lecturer who taught PL/SQL after I gave a presentation at a conference. My talk was on PL/SQL good practice; one of my slides simply said "Don't use triggers". The lecturer told me he finds such advice difficult to reconcile with the needs of the curriculum. They have to teach their students all the syntax but he admitted that often they set tasks which demanded solutions we wouldn't use when writing software professionally.

This is such a question. The correct approach is to use a check constraint, as Gordon's answer shows. Constraints are more efficient, and idiomatic too. But your teacher wants you to write a trigger, so here is your code, corrected.

CREATE OR REPLACE TRIGGER CheckAdvance
    BEFORE INSERT OR UPDATE OF advance ON titles
    FOR EACH ROW
BEGIN
    IF :new.advance < 0 
    THEN
        raise_application_error(-20000
                , 'Advance cannot be less than zero');
    ELSIF :new.advance > 100
    THEN
        raise_application_error(-20001
                , 'Advance cannot be greater than one hundred.');
    END IF;
END;

Points to note:

1. CREATE OR REPLACE means we can change the trigger code without a preliminary DROP statement.
2. BEGIN and END frame blocks of code such as trigger bodies.
3. Static conditionals are framed with IF ... END IF keywords; WHEN is for exiting loop constructs. Not strictly true, see my update below.
4. Reference table column values with :NEW (and :OLD) keywords - note the colon.
5. Use RAISE_APPLICATION_ERROR to throw an exception; the error number must be in the range -20999 to -20000 which Oracle reserves for user-defined exceptions.
6. Make your error message meaningful: tell your users what they did wrong instead of making them guess.
7. Learn to use indentation to make your code readable. Your future co-workers will thank you for it.

---

@yadipp reminded me that the WHEN clause used in the Seeker's question is valid syntax, so belatedly I'm extending my solution to show how that would look. The most important thing to note is that NEW and OLD aren't treated as bind variables and so don't take a :.

CREATE OR REPLACE TRIGGER CheckAdvance
    BEFORE INSERT OR UPDATE OF advance ON titles
    FOR EACH ROW
    WHEN (new.advance < 0 OR new.advance > 100)
BEGIN
    raise_application_error(-20000
                , 'Advance cannot be less than zero or greater than one hundred.');
END;

(And I tweaked my original solution to show one reason to use IF in the trigger body: to handle different conditions differently.)

SQL: trigger to prevent inserting a row into a table based on a condition

I'd rearrange the logic here and:

1. Check if the driver has tested the vehicle, then
2. Check if the review is attempted before the testing end date for the vehicle (something you've left out).

In Oracle PL/SQL, which includes trigger code, you can't just SELECT. You have to SELECT INTO a variable. Then you can use the variable in your logic.

Equally important, when you SELECT INTO a variable the query can only return one result. Multiple rows will trigger the error you've encountered.

CREATE OR REPLACE TRIGGER review_check_validity
AFTER INSERT ON review
FOR EACH ROW
DECLARE
    testEnd DATE;
    vehicleTestCount NUMBER;
BEGIN

    SELECT COUNT(*)
      INTO vehicleTestCount
      FROM testing
      WHERE vehicle_id = :new.vehicle_id;

    IF vehicleTestCount = 0 THEN
      raise_application_error(-20000, 'Driver has never driven this vehicle');
    END IF;

    -- Assumes one test per driver per vehicle
    SELECT testing_end
      INTO testEnd
      FROM testing
      WHERE driver_no = :new.driver_no
        AND vehicle_id = :new.vehicle_id;

    IF :new.review_date < testEnd THEN
      raise_application_error(-20000, 'Review date cannot be before 
    testing end date');

    END IF;

END;
/

Finally, your table structure allows multiple tests of the same vehicle by the same driver. If it should allow this, then the review table should link to the testing table by testing_id rather than driver_no and vehicle_id.

MySQL Trigger: Prevent Insert by IF statement

You have a few missing commas and some made up sql and a missing delimter at the end.

DELIMITER $$

CREATE TRIGGER checkSubscribeTimeCourse 
BEFORE INSERT ON course_student
FOR EACH ROW
BEGIN

    IF (SELECT COUNT(*) FROM capacity c, course_capacity cc 
        WHERE c.cid = cc.cid 
        AND cc.cid = new.cid
        AND (c.end >= CURRENT_TIMESTAMP OR c.end IS NULL)
        AND (c.start <= CURRENT_TIMESTAMP OR c.start IS NULL)<1)
    THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Error';
    END IF;

END$$

DELIMITER ;

Trigger to block insert into SQL Server

Remove the ROLLBACK from the trigger and raise and error with severity 11 or higher so that the stored procedure CATCH block is entered. The code below also uses the simplified version of THROW to reraise the trigger error instead of throwing a new one and uses EXISTS to handle multi-row inserts.

alter proc test_insert @id int, @name nvarchar(10)
as
begin
    BEGIN TRY
        BEGIN TRANSACTION
        insert into test1 (id,name) values(@id,@name)
        insert into test (id,name) values(@id,@name)
       COMMIT
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;
        THROW;
    END CATCH
end
GO
alter TRIGGER TRG_test
ON dbo.test
AFTER INSERT AS
BEGIN
    if EXISTS(select 1 from inserted WHERE id = 1)
        begin
        RAISERROR('error', 16,1);
        RETURN  
    end
END
GO

---

Related Topics