SQL HELP

[desosav]

exo mia ergasia gia sql i opia tha itan apli an ixa parakolouthisi sta teleftea mathimata....tespa.... opios bori na voithisi me ta triggers tha tou imoun evgnomon!!!

Use the database “testme”
Create a table called customers
(custid identity primary key int not null, name varchar(30), gender char(6))

Create another table called orders
(ordid identity primary key int not null, ordate datetime not null, custid int not null, tr_amount money not null)

Insert three customers, and for two of them create two transactions
Create a trigger which will disable someone to insert a customer by specifying the custid
Create a trigger which will insert its own custid although someone will try to insert a customer by specifying the custid
Create a trigger which will cancel a user’s attempt to delete a customer if the customers has respective records in the orders table

[Deggial]

Βασική ερώτηση: Σε τι σύστημα; Oracle;

[desosav]

Βασική ερώτηση: Σε τι σύστημα; Oracle;

MS sql server

[Deggial]

Δεν έχω χρησιμοποιήσει MS SQL server, μόνο Oracle, και η σύνταξη των triggers διαφέρει. Θα κοιτάξω αύριο να δω τι γίνεται κι αν μπορώ θα σου απαντήσω.

[desosav]

:shock: thanx man...tha sou ime ipoxreos!!! :D :D

[Kafroulis]

nai vevaia kai tha eisai :rofl:

[IrDA]

kafroulis esu eisai sto avatar ?

[dead]

kafroulis esu eisai sto avatar ?

OFF_TOPIK. ena lock twra

:***

[Deggial]

Με μια πρόχειρη εξέταση της MS SQL, τα παρακάτω πρέπει να δουλεύουν. Δεν έχω MS SQL server για να το δοκιμάσω, ωστόσο, οπότε μπορεί να είναι ελλειπή ή να απαιτούν διορθώσεις.




-- Empodizei ti dimiourgia pelati an dinetai to custid
CREATE TRIGGER disable_insert
ON customers
INSTEAD OF INSERT
AS
IF UPDATE(custid) -- elegxos an ginetai insert tou custid
BEGIN
RETURN -- aplws de ginetai tipote
END
GO

-- Allazei to dwthen custid
CREATE TRIGGER insert_with_custid
ON customers
INSTEAD OF INSERT
AS
DECLARE @newcustid AS INT
-- i timi tou newcustid tha einai to megalytero yparxwn custid + 1
SELECT @newcustid = MAX(custid)+1 FROM customers

-- Kanoume pleon tin prosthiki
INSERT INTO customers VALUES(@newcustid, name, gender)
GO

-- De svinei pelati an exei anoiktous logariasmous
CREATE TRIGGER check_deletion
ON customers
FOR DELETE
AS
IF EXISTS
(SELECT 1 FROM orders WHERE orders.custid = customers.custid)
BEGIN
ROLLBACK -- de ginetai to delete
END
GO

[Kafroulis]

kafroulis esu eisai sto avatar ?
nai egw eimai...

[desosav]

@Deggial: xilia efxaristo gia tin voithia...tha to psakso argotera!!!
@irda: she is the one!!