1

Topic: How to create the trigger which checks record on uniqueness

How to create the trigger which checks that at each client it has not been opened in one currency more than one bank account.
The table:
CREATE TABLE Account (
account_number bigint NOT NULL;
c_identification_code bigint NOT NULL;
account_type varchar (10) NOT NULL CHECK (account_type IN (' salary ', ' depositary '));
currency char (3) CHECK (currency IN (' UAH ', ' USD ', ' EUR '));
opening_an_account date NOT NULL;
account_balance money NOT NULL DEFAULT 0.00;
snapshot_date date;
PRIMARY KEY (snapshot_date, account_number);
FOREIGN KEY (c_identification_code) REFERENCES ustomers (identification_code))

2

Re: How to create the trigger which checks record on uniqueness

to check?  the trigger? Unique index on (the user, the currency)  eliminates appearances of the second record at the user in the same currency.

3

Re: How to create the trigger which checks record on uniqueness

Akina, so a task in view

4

Re: How to create the trigger which checks record on uniqueness

cglcz;
Most easier to create unique index on this field.
It will be more effective than the trigger.
It is possible and  UNIQUE. But also in this case the server automatically creates unique index.

5

Re: How to create the trigger which checks record on uniqueness

cglcz wrote:

Akina so a task in view

Show that you already made.

6

Re: How to create the trigger which checks record on uniqueness

cglcz wrote:

How to create the trigger which checks

And now on letters.
The trigger upon performance of certain actions dataful fulfills certain actions or in addition to what caused its actuating, or instead of them.
And here to what bum to stick "check"? Well checked up... Further that?

7

Re: How to create the trigger which checks record on uniqueness

Akina;
Well set to the person such task that to rest that? smile
Proceeding from setting if try to add in the table of an abacus, and at the client already is;
That to deduce the error report.
that...

8

Re: How to create the trigger which checks record on uniqueness

wrote:

Akina;
Well set to the person such task that to rest that? smile
Proceeding from setting if try to add in the table of an abacus, and at the client already is;
That to deduce the error report.
that...

It is possible not to add only "wrong" accounts, it is possible to cancel generally all insertion, other variants are possible also.
Besides, and in case of an update too it is necessary to check.
In general, there is no clearness in setting.
And nevertheless, cglcz, it is required to invent for you setting of the task and to solve it "on a turn-key basis"
Or to correct any your decision and implementation? In that case show it to us.

9

Re: How to create the trigger which checks record on uniqueness

;
FOR = AFTER
FOR INSTEAD OF - such does not happen

10

Re: How to create the trigger which checks record on uniqueness

cglcz;
And than the unique index does not approach?

11

Re: How to create the trigger which checks record on uniqueness

, passing by wrote:

cglcz;
And than the unique index does not approach?

that the teacher to a lab does not include.

12

Re: How to create the trigger which checks record on uniqueness

CREATE TRIGGER [dbo]. [trg] ON [dbo]. [Account]
FOR INSERT, UPDATE
as
begin
if exists
(
select 1 from Account an inner join inserted i
on a.c_identification_code = i.c_identification_code
and a.account_type = i.account_type
and a.currency = i.currency
)
begin
select ' Error'
end
end
While here such sketch turned out, but underlines red all first line
CREATE TRIGGER [dbo]. [trg] ON [dbo]. [Account]

13

Re: How to create the trigger which checks record on uniqueness

cglcz wrote:

but underlines red all first line

Before it in a window of anything is not present?

14

Re: How to create the trigger which checks record on uniqueness

cglcz;
So you probably already have such trigger

IF OBJECT_ID (N ' [dbo]. [trg] ', N'TR ') IS NOT NULL
DROP TRIGGER [dbo]. [trg];
GO
CREATE TRIGGER [dbo]. [trg]
......

15

Re: How to create the trigger which checks record on uniqueness

xenix;
No, precisely is not present

16

Re: How to create the trigger which checks record on uniqueness

All turned out, all thanks for the help)

17

Re: How to create the trigger which checks record on uniqueness

cglcz wrote:

all turned out, all thanks for the help)

I  wildly  BUT what secret sense of such trigger?

CREATE TRIGGER [dbo]. [trg] ON [dbo]. [Account]
FOR INSERT, UPDATE
as
begin
if exists
(
select 1 from Account an inner join inserted i
on a.c_identification_code = i.c_identification_code
and a.account_type = i.account_type
and a.currency = i.currency
)
begin
select ' Error'
-- Well  it to you  and  further? Action happened and remains in the table
end
end

18

Re: How to create the trigger which checks record on uniqueness

cglcz;
You in course, what trigger FOR INSERT, UPDATE works after an insertion/change?
By this moment of record are already interposed!
To compare inserted to the table senselessly!
It will always give TRUE

19

Re: How to create the trigger which checks record on uniqueness

iap wrote:

cglcz;
You in course, what trigger FOR INSERT, UPDATE works after an insertion/change?
By this moment of record are already interposed!
To compare inserted to the table senselessly!
It will always give TRUE

And here to consider the trigger INSTEAD OF INSERT, UPDATE very much even costs!