On Tuesday, May 3, 2016, drum.lu...@gmail.com <mailto:drum.lu...@gmail.com>
<drum.lu...@gmail.com
<mailto:drum.lu...@gmail.com>> wrote:
* This is what I did...
|-- Creating the table
CREATE TABLE public.company_seqs
(company_id BIGINTNOT NULL,
last_seq BIGINTNOT NULL DEFAULT 1000,
CONSTRAINT company_seqs_pkPRIMARY KEY (company_id)
);
-- Creating the function
CREATE OR REPLACEFUNCTION users_code_seq()
RETURNS"trigger" AS
'
BEGIN
UPDATE public.company_seqs
SET last_seq = (last_seq + 1)
WHERE company_id = NEW.company_id;
SELECT INTO NEW.code last_seq
FROM public.company_seqs WHERE company_id =
NEW.company_id;
END IF;
RETURN new;
END
'
LANGUAGE'plpgsql' VOLATILE;
-- Creating the trigger
CREATE TRIGGER tf_users_code_seq
BEFOREINSERT
ON public.users
FOR EACHROW
EXECUTE PROCEDURE users_code_seq();|
1) I just may be over-sensitive to this, but after Adrian Klaver referred you to a ten-years old
post that the above looks an awful lot similar too, it sure would be nice to see some attribution
rather than claiming it as your own with "...what *I* did..."
When inserting data:
|INSERT INTO
public.users(id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id)
VALUES (672,'te...@test.com
<javascript:_e(%7B%7D,'cvml','te...@test.com');>','bucefalo','0','2016-05-03
00:01:01','2016-05-03 00:01:01',default,'1');
INSERT INTO
public.users(id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id)
VALUES (672,'te...@test.com
<javascript:_e(%7B%7D,'cvml','te...@test.com');>','bucefalo','0','2016-05-03
00:01:01','2016-05-03 00:01:01','inserting my own data code column','1');|
*
On the first query, nothing happens on the users.code column. The
column is null.
*
On the second query, I can see the "inserting my own data code column"
inserted into the
code column. |This means my Trigger function is not working.. I don't
know why.|
2) Does the public.company_seqs have any rows in it?
3) Not sure you need a separate company_seq table. Since there is a one-to-one relation between
company and company_seqs, put the last_seq column in the company table.
-- Berend
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
