Trigger + Access + Sql -- different values?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Debbie Morrow - 11 Feb 2004 18:20 GMT

I've got an odd question ..

I have an application with an Access 2002 front end with
linked tables to a SQL 2000 backend. Users have always
been able to edit the SQL tables via access linked
tables/forms with no problem. I have now created an audit
table and using triggers to register each change. The
problem is, users want to be able to view the audit tables
through an access linked table .. and for some reason the
data in the audit table is not the same as the data on the
SQL server. However, if I import a copy of the audit table
the data is exactly the same. For example, if I make a
change to the column subtitle, the SQL table view will
show:

Subtitle        .....        auditAction
Testing                Snapshot A
Testing 1                SnapShot B

An imported copy of the table into Access will show:

Subtitle        .....        auditAction
Testing                Snapshot A
Testing 1                SnapShot B

While the linked Access table with show:

Subtitle        ....        auditAction
Testing                Snapshot A
Testing                Snapshot A

It doesn't make sense to me that the linked SQL table does
not show the same data as an imported table? Is there
caching going on somewhere?

The trigger is:

CREATE TRIGGER [TRIGGERPLAN2005] ON [dbo].[tblPlan2005]
FOR INSERT, UPDATE, DELETE
AS
IF (SELECT COUNT(*) FROM INSERTED) > 0 AND (SELECT COUNT
(*) FROM DELETED) > 0 BEGIN INSERT auditPlan2005
(auditUniqueID,auditQuarter,auditSubjectCourseAssign,auditL
Section,auditSubtitle, auditInstructorID,
auditInstructorPost, auditSupervise, auditEnrollLimit,
auditThirdWeekEnroll, auditNonUSNonEU, auditFilmCourse,
auditNotes,auditUser,auditDate, auditAction) SELECT
UniqueID, Quarter, SubjectCourseAssign, LSection,
Subtitle, InstructorID, InstructorPost, Supervise,
EnrollLimit, ThirdWeekEnroll, NonUSNonEU, FilmCourse,
Notes, User_Name(), GetDate(), 'SNAPSHOT A' From deleted
INSERT auditPlan2005
(auditUniqueID,auditQuarter,auditSubjectCourseAssign,auditL
Section,auditSubtitle, auditInstructorID,
auditInstructorPost, auditSupervise, auditEnrollLimit,
auditThirdWeekEnroll, auditNonUSNonEU, auditFilmCourse,
auditNotes,auditUser,auditDate,auditAction)
SELECT UniqueID, Quarter,SubjectCourseAssign,LSection,
Subtitle, InstructorID, InstructorPost, Supervise,
EnrollLimit, ThirdWeekEnroll, NonUSNonEU, FilmCourse,
Notes, User_Name(), GetDate(), 'SNAPSHOT B' From inserted
END ELSE IF (SELECT COUNT(*) FROM INSERTED) > 0 BEGIN
INSERT auditPlan2005
(auditUniqueID,auditQuarter,auditSubjectCourseAssign,auditL
Section,auditSubtitle,auditInstructorID,
auditInstructorPost, auditSupervise, auditEnrollLimit,
auditThirdWeekEnroll, auditNonUSNonEU, auditFilmCourse,
auditNotes,auditUser,auditDate, auditAction) SELECT
UniqueID, Quarter, SubjectCourseAssign, LSection,
Subtitle, InstructorID, InstructorPost, Supervise,
EnrollLimit, ThirdWeekEnroll, NonUSNonEU, FilmCourse,
Notes, User_Name(), GetDate(), 'ADDED' From inserted END
ELSE IF (SELECT COUNT(*) FROM DELETED) > 0 INSERT
auditPlan2005
(auditUniqueID,auditQuarter,auditSubjectCourseAssign,auditL
Section,auditSubtitle, auditInstructorID,
auditInstructorPost, auditSupervise, auditEnrollLimit,
auditThirdWeekEnroll, auditNonUSNonEU, auditFilmCourse,
auditNotes,auditUser,auditDate, auditAction) SELECT
UniqueID, Quarter, SubjectCourseAssign, LSection,
Subtitle, InstructorID, InstructorPost, Supervise,
EnrollLimit, ThirdWeekEnroll, NonUSNonEU, FilmCourse,
Notes, User_Name(), GetDate(), 'REMOVED' From deleted ENDIF

Reply to this Message

- 11 Feb 2004 20:51 GMT

Answered my own question .. the audit tables needed an
Identity column in order to register the update, delete
and inserts corrected on the linked tables in Access.

-debbie

>-----Original Message-----
>I've got an odd question ..
[quoted text clipped - 39 lines]
>IF (SELECT COUNT(*) FROM INSERTED) > 0 AND (SELECT COUNT
>(*) FROM DELETED) > 0 BEGIN INSERT auditPlan2005

(auditUniqueID,auditQuarter,auditSubjectCourseAssign,auditL

>Section,auditSubtitle, auditInstructorID,
>auditInstructorPost, auditSupervise, auditEnrollLimit,
[quoted text clipped - 5 lines]
>Notes, User_Name(), GetDate(), 'SNAPSHOT A' From deleted
>INSERT auditPlan2005

(auditUniqueID,auditQuarter,auditSubjectCourseAssign,auditL

>Section,auditSubtitle, auditInstructorID,
>auditInstructorPost, auditSupervise, auditEnrollLimit,
[quoted text clipped - 6 lines]
>END ELSE IF (SELECT COUNT(*) FROM INSERTED) > 0 BEGIN
>INSERT auditPlan2005

(auditUniqueID,auditQuarter,auditSubjectCourseAssign,auditL

>Section,auditSubtitle,auditInstructorID,
>auditInstructorPost, auditSupervise, auditEnrollLimit,
[quoted text clipped - 6 lines]
>ELSE IF (SELECT COUNT(*) FROM DELETED) > 0 INSERT
>auditPlan2005

(auditUniqueID,auditQuarter,auditSubjectCourseAssign,auditL

>Section,auditSubtitle, auditInstructorID,
>auditInstructorPost, auditSupervise, auditEnrollLimit,
[quoted text clipped - 6 lines]
>
>.

Reply to this Message

sudhindra - 29 Jun 2009 18:05 GMT

can some one plz help me to convert the below code to sqlserver

CREATE OR REPLACE TRIGGER user_account_status_trg
AFTER INSERT OR UPDATE
OF STATUS
ON USER_ACCOUNT
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
c_job_id NUMERIC(16);
BEGIN
IF INSERTING THEN
INSERT INTO user_account_status_log (user_account_id, status, start_date_time, tenant_id) VALUES
(:NEW.user_account_id, :NEW.status, SYSDATE, :NEW.tenant_id);
ELSE
IF :NEW.status != :OLD.status THEN
INSERT INTO user_account_status_log (user_account_id, status, start_date_time, end_date_time, tenant_id) SELECT

:NEW.user_account_id, :OLD.status, :OLD.status_change_date, SYSDATE, :NEW.tenant_id FROM dual

WHERE NOT EXISTS (select 1 from user_account_status_log where user_account_id = :NEW.user_account_id and end_date_time is null);
UPDATE user_account_status_log set end_date_time = SYSDATE where user_account_id = :NEW.user_account_id and end_date_time is null;
INSERT INTO user_account_status_log (user_account_id, status, start_date_time, tenant_id) VALUES
(:NEW.user_account_id, :NEW.status, SYSDATE, :NEW.tenant_id);
END IF;
END IF;
END;

From http://www.google.co.in/search?hl=en&rlz=1G1GGLQ_ENIN330&q=example+IF+(SELECT+CO
UNT(*)+FROM+inserted)+>+0+AND+(SELECT+COUNT(*)+FROM+deleted)+=+0&meta=&aq=f&oq

Posted via DevelopmentNow.com Group
http://www.developmentnow.com/g/

Reply to this Message

Hugo Kornelis - 03 Jul 2009 00:01 GMT

>can some one plz help me to convert the below code to sqlserver
>
[quoted text clipped - 22 lines]
>END;
>/

Hi sudhindra,

This looks like Oracle syntax to me. And translating an Oracle trigger
to T-SQL syntax would be a terrible idea, because of some fundamental
differences between the two products.

The better thing to do is to describe what needs to be done on insert
and update of the User_Account table, and then code new trigger in T-SQL
to do just that. The end result would probably look somewhat like this:

CREATE TRIGGER User_Account_Status_Trg_INS
AFTER INSERT ON User_Account
AS
IF @@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
INSERT INTO user_account_status_log
(user_account_id, status, start_date_time, tenant_id)
SELECT user_account_id, status, CURRENT_TIMESTAMP, tenant_id
FROM inserted;
go

CREATE TRIGGER User_Account_Status_Trg_UPD
AFTER UPDATE ON User_Account
AS
IF @@ROWCOUNT = 0 RETURN;
IF NOT UPDATE(Status) RETURN;
SET NOCOUNT ON;
-- If on SQL Server 2008, replace the UPDATE and the first INSERT
-- with a single MERGE statement.
UPDATE user_account_status_log
SET end_date_time = CURRENT_TIMESTAMP
WHERE EXISTS
(SELECT *
FROM inserted AS i
WHERE i.user_account_id = user_account_status_log.user_account_id)
AND end_date_time IS NULL;
INSERT INTO user_account_status_log
(user_account_id, status, start_date_time,
end_date_time, tenant_id)
SELECT i.user_account_id, d.status, d.status_change_date,
CURRENT_TIMESTAMP, i.tenant_id
FROM inserted AS i
INNER JOIN deleted AS d
ON d.user_account_id = i.user_account_id
WHERE NOT EXISTS
(SELECT *
FROM user_account_status_log AS l
WHERE l.user_account_id = i.user_account_id
AND l.end_date_time IS NULL);

INSERT INTO user_account_status_log
(user_account_id, status, start_date_time, tenant_id)
SELECT user_account_id, status, CURRENT_TIMESTAMP, tenant_id
FROM inserted;
go

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply).

Signature

Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Reply to this Message