# TK2 Basdat Jawaban

## Wajib
### Soal Trigger 1 **(FORMAT = TK2_A3_1)**

CREATE OR REPLACE FUNCTION pendaftaran()
RETURNS TRIGGER as 
$$
BEGIN
	IF NEW.password ~ '[0-9]' AND NEW.password ~ '[A-Z]' THEN
   		RETURN NEW;
	ELSE
   		RAISE EXCEPTION 'Password harus memiliki minimal 1 huruf kapital dan 1 angka!';
	END IF;
END;
$$
LANGUAGE plpgsql;


CREATE TRIGGER triggerPendaftaran
BEFORE INSERT ON USER_ACC
FOR EACH ROW EXECUTE PROCEDURE pendaftaran();

#### Example

INSERT INTO USER_ACC VALUES ('tk2a3basdat.com','abcdef',0123456789,'Kelompok','A3');
INSERT INTO USER_ACC VALUES ('tk2a3basdat.com','0bcdeF',0123456789,'Kelompok','A3');

## Emir
### Soal Trigger 4 **(FORMAT = TK2_A3_2106632541_2)**

CREATE OR REPLACE FUNCTION cek_saldo_penarikan_restopay()
RETURNS TRIGGER AS
$$	
	BEGIN
		IF(NEW.RestoPay < OLD.RestoPay AND OLD.RestoPay - NEW.RestoPay > OLD.RestoPay) THEN
			RAISE EXCEPTION 'Nominal penarikan saldo tidak boleh melebihi jumlah saldo yang dimiliki saat ini';
		END IF;
		RETURN NEW;
	END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER trigger_cek_saldo_penarikan_restopay
BEFORE UPDATE OF RestoPay ON TRANSACTION_ACTOR
FOR EACH ROW
EXECUTE FUNCTION cek_saldo_penarikan_restopay();

#### Example

UPDATE TRANSACTION_ACTOR SET RestoPay = -1000 WHERE Email='[e-mail]';

## Alvaro
### Soal Trigger 3 **(FORMAT = TK2_A3_2106752180_3)**

CREATE OR REPLACE FUNCTION checkDeliveryFeePerKm()
RETURNS TRIGGER as 
$$
DECLARE
	motorFee integer;
	carFee integer;
BEGIN
	motorFee := NEW.motorfee;
	carFee := NEW.carFee;
	
	IF(motorFee >= carFee) THEN
		RAISE EXCEPTION 'Biaya pengiriman dengan motor harus lebih rendah daripada biaya pengiriman dengan mobil';
	ELSIF ((motorFee not BETWEEN 2000 AND 7000) OR (carFee not BETWEEN 2000 AND 7000)) THEN
		RAISE EXCEPTION 'Harus berada diantara 2000 dan batas 7000';

	END IF;
	RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER triggercheckDeliveryFeePerKm
BEFORE INSERT OR UPDATE ON DELIVERY_FEE_PER_KM
FOR EACH ROW EXECUTE PROCEDURE checkDeliveryFeePerKm();

#### Example

INSERT INTO DELIVERY_FEE_PER_KM VALUES (12, 'Makassar', 3000, 6969); 
UPDATE DELIVERY_FEE_PER_KM SET motorFee = 8000 WHERE id = '12';

## Dian
### Soal Trigger 4 **(FORMAT = TK2_A3_2106702150_4)**

CREATE OR REPLACE FUNCTION deliveryPrice()
RETURNS TRIGGER as 
$$
DECLARE
	mFee integer;
	cFee integer;
BEGIN
	SELECT MotorFee, CarFee INTO mFee, cFee
	FROM DELIVERY_FEE_PER_KM
	WHERE Id=NEW.DFId;
	
	IF (NEW.VehicleType='Car') THEN
		NEW.DeliveryFee = cFee * 2;
	ELSE
		NEW.DeliveryFee = mFee * 2;
	END IF;

	NEW.TotalPrice = NEW.TotalFood + NEW.DeliveryFee;

	RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER triggerDeliveryPrice
BEFORE INSERT ON TRANSACTION
FOR EACH ROW
EXECUTE PROCEDURE deliveryPrice();

#### Example

INSERT INTO TRANSACTION VALUES ('[e-mail]','2022-10-03 08:15:10','East','Stremmiarnilt Avenue','Ruyigi','South Island',4000.00,0.0,0.0,30000.00,5,'4','1','2','[e-mail]','Car');

## Fauzan
### Soal Trigger 5 **(FORMAT = TK2_A3_2106702150_5)**

CREATE OR REPLACE FUNCTION menambah_restopay()
RETURNS TRIGGER as
$$
DECLARE
    biaya_pengantaran integer;
    total_harga_makanan integer;
    id_kurir text;
    email_restoran text;
BEGIN
    IF(NEW.name = 'Pesanan Selesai') THEN
        SELECT tr.deliveryFee, (tr.totalprice - tr.deliveryFee), tr.courierid, res.email 
        INTO biaya_pengantaran, total_harga_makanan, id_kurir, email_restoran
        FROM transaction tr 
        JOIN transaction_history th on tr.email = th.email 
        JOIN transaction_status ts on ts.id = th.tsid
        JOIN transaction_food tf on tf.email = tr.email and tf.datetime = tr.datetime
        JOIN food f on f.rname = tf.rname and f.rbranch = tf.rbranch and f.foodname = tf.foodname
        JOIN restaurant res on res.rname = f.rname and res.rbranch = f.rbranch;

        UPDATE transaction_actor set restopay = restopay + biaya_pengantaran
        WHERE email = id_kurir;

        UPDATE transaction_actor set restopay = restopay + total_harga_makanan
        WHERE email = email_restoran;
    END IF;
    RETURN new;
END;
$$ language plpgsql;



CREATE TRIGGER trigger_menambah_restopay
AFTER update of name on transaction_status
FOR EACH ROW
EXECUTE PROCEDURE menambah_restopay();

#### Example

insert into transaction_history values ('[e-mail]','2022-08-15 9:23:03','TS4', current_timestamp);