May 18, 2009 at 7:49 am
Hi,
I am trying to populate a table using an update trigger so that when a members payment status changes to 'Expelled' or 'Resigned' it will insert that record into the new table. This is so that I can track the amount of members that leave our organisation in a given month.
Ideally it would be nice to have the old value and new value populated in the new table but it is not a must have.
The fields I want are held in the accservcategory table and are accountid(PK), paymentstatus, paymentstatuschangedate.
Could anyone provide me with the syntax for the trigger?
Thanks in advance...
Tarran
May 18, 2009 at 8:18 am
can you provide the CREATE TABLE definition for the table accservcategory ?
Lowell
May 18, 2009 at 8:22 am
First I suggest you read this article[/url] (disclaimer, I wrote it).
Then I suggest you read the first article in my signature line if you want code examples. The best I can do without that is something like this:
CREATE TRIGGER trg_table_name_upd ON table_name
FOR UPDATE
AS
SET NOCOUNT ON;
-- only do it if the status column has changed
IF UPDATE(column_name)
BEGIN
/*
if you like you can actually omit the IF EXISTS check as the insert
with the where clause will keep any incorrect inserts from happening
*/
IF EXISTS(SELECT * FROM inserted WHERE column_name IN ('Expelled', 'Resigned'))
BEGIN
INSERT INTO new_table
SELECT
columns
FROM
inserted
WHERE
column_name IN ('Expelled', 'Resigned');
END
END
RETURN
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 18, 2009 at 8:24 am
Is this what you mean?
CREATE TABLE [sysdba].[QG_ACCSERVCATEGORY](
[QG_ACCSERVCATEGORYID] [char](12) NOT NULL,
[ACCOUNTID] [char](12) NOT NULL,
[CREATEUSER] [char](12) NULL,
[CREATEDATE] [datetime] NULL,
[MODIFYUSER] [char](12) NULL,
[MODIFYDATE] [datetime] NULL,
[JOINDATE] [datetime] NULL,
[LASTCLEANEDDATE] [datetime] NULL,
[LASTCLEANEDUSER] [varchar](80) NULL,
[LEAVINGDATE] [datetime] NULL,
[PAYMENTSTATUS] [varchar](32) NULL,
[PAYMENTSTATUSCHANGEREASON] [varchar](50) NULL,
[PAYMENTSTATUSDATE] [datetime] NULL,
[RENEWALDATE] [datetime] NULL,
[SIGNUPDATE] [datetime] NULL,
[CERTIFIEDDATE] [datetime] NULL,
[QG_SERVCATEGORYID] [varchar](12) NULL,
[MILLERORGID] [varchar](10) NULL,
[APPLICATIONSOURCE] [varchar](64) NULL
) ON [PRIMARY]
May 18, 2009 at 9:25 am
Jack Corbett (5/18/2009)
First I suggest you read this article[/url] (disclaimer, I wrote it).Then I suggest you read the first article in my signature line if you want code examples. The best I can do without that is something like this:
CREATE TRIGGER trg_table_name_upd ON table_name
FOR UPDATE
AS
SET NOCOUNT ON;
-- only do it if the status column has changed
IF UPDATE(column_name)
BEGIN
/*
if you like you can actually omit the IF EXISTS check as the insert
with the where clause will keep any incorrect inserts from happening
*/
IF EXISTS(SELECT * FROM inserted WHERE column_name IN ('Expelled', 'Resigned'))
BEGIN
INSERT INTO new_table
SELECT
columns
FROM
inserted
WHERE
column_name IN ('Expelled', 'Resigned');
END
END
RETURN
That worked perfectly. Many thanks for your help...
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply