about Triggers ?

dzconception

10y1kDatabases & DBA

good morning

ih vae a firebird database in c#

i have 2 tables patients and consultations

i want create trigger block delete if patient have consultation

so if code_pat in consultations table

thank you

good day

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke10y ago
Why not just create a foreign key constraint, which will automatically restrict the deletion and you (hopefully) will also get a trappable exception which you can convert into a use friendly message.
dzconception10y ago
this triger is writen in sqlserver i want it in firbird please ?
Rajeev Punhani10y ago
Hi,
CREATETRIGGER[dbo].[patient_InsteadOfDELETE]
ON[dbo].[patient]
INSTEADOFDELETE
AS
BEGIN
SETNOCOUNTON;
DECLARE@patientidINT
SELECT@patientid=DELETED.patientid
FROMDELETED
IF(selectcount(*)fromconsultationswherecode_pat=@patientid2>0)
BEGIN
RAISERROR('Recordcannotbedeleted',16,1)
ROLLBACK
END
ELSE
BEGIN
DELETEFROMpatient
WHEREPatientId=@patientid
END
END
please use the column namesbased on your table.
dzconception10y ago
thank yoy again but i have probleme with this code in firebird on declare
SET TERM ^ ;

CREATE TRIGGER TRIGGER01 FOR PATIENTS
ACTIVE BEFORE DELETE POSITION 0
AS
BEGIN
SETNOCOUNTON
DECLARE VARIABLE patientid INTEGER
begin
SELECT patientid=DELETED.patientid
FROMDELETED
IF(selectcount(*)fromconsultationswherecode_pat=@patientid2>0)
BEGIN
RAISERROR('Recordcannotbedeleted',16,1)
ROLLBACK
END
ELSE
BEGIN
DELETEFROMpatient
WHEREPatientId=@patientid
END
END
end^

SET TERM ; ^
Rajeev Punhani10y ago
Hi,
Please see the below link
http://www.aspsnippets.com/Articles/SQL-Server-Instead-Of-Delete-Trigger-Example.aspx
Bikesh Srivastava10y ago
Hi,
you don't need to create trigger,you can use merge query to resolve your problem.
https://bikeshsrivastava.blogspot.in/2016/07/part-28-what-is-merge-query-in-sql.html