1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76
|
CREATE TRIGGER [dbo].[TG_CBUPD_F_DOCREGL] ON [dbo].[F_DOCREGL] FOR UPDATE AS
BEGIN
DECLARE @sGUID uniqueidentifier;
BEGIN TRY
SET NOCOUNT ON;
IF dbo.CB_FntIsFileLock('F_DOCREGL',3) = 1
Raiserror(80004,11,1);
IF dbo.CB_FntIsFileLock('F_DOCREGL',0) = 1
Raiserror(80008,11,1);
IF dbo.CB_FntIsFileLock('F_DOCREGL',1) = 1
Raiserror(80008,11,1);
IF EXISTS(SELECT 'a' FROM inserted where dbo.CB_FntIsRecordLock('F_DOCREGL',cbMarq) = 1)
Raiserror(80003,11,1);
IF EXISTS(SELECT 'a' FROM cbUserSession WHERE cbSession != @@SPID AND cbCurrentSynchroType != 0 AND CB_Type = 'CIAL')
Raiserror(80012,11,1);
IF NOT UPDATE(cbModification) AND UPDATE(cbReplication)
UPDATE F_DOCREGL SET cbModification = convert(datetime2(0) , GetDate()),cbReplication = inserted.cbReplication FROM inserted WHERE F_DOCREGL.cbMarq = inserted.cbMarq;
IF NOT UPDATE(cbReplication) AND UPDATE(cbModification)
UPDATE F_DOCREGL SET cbModification = inserted.cbModification,cbReplication = (SELECT ISNULL(CB_Replication,0) FROM cbSysTable WHERE CB_Type = 'CIAL') FROM inserted WHERE F_DOCREGL.cbMarq = inserted.cbMarq;
IF NOT UPDATE(cbReplication) AND NOT UPDATE(cbModification)
UPDATE F_DOCREGL SET cbModification = convert(datetime2(0) , GetDate()),cbReplication = (SELECT ISNULL(CB_Replication,0) FROM cbSysTable WHERE CB_Type = 'CIAL') FROM inserted WHERE F_DOCREGL.cbMarq = inserted.cbMarq;
IF UPDATE(cbFlag)
BEGIN
DELETE FROM cbSysLogRecord FROM cbSysLogRecord JOIN inserted ON (inserted.cbFlag & 1) = 0 AND cbSysLogRecord.cbMarq = inserted.cbMarq WHERE cbFile = 'F_DOCREGL';
IF NOT UPDATE(cbReplication)
BEGIN
IF (SELECT COUNT(*) FROM inserted JOIN deleted ON (deleted.cbFlag & 1) <> 1 AND deleted.cbMarq = inserted.cbMarq WHERE (inserted.cbFlag & 1) = 1) > 0
BEGIN
SELECT @sGUID = CB_LocalGUID FROM cbSysTable WHERE CB_Type = 'CIAL';
INSERT cbSysLogRecord (CB_Operation,CB_DateOperation,CB_Creator,CB_SiteOrigine,CB_Replication,CB_Identifiant,cbMarq,cbFile) SELECT 1,GetDate(),inserted.cbCreateur,@sGUID,inserted.cbReplication,NEWID(),inserted.cbMarq,'F_DOCREGL' FROM inserted JOIN deleted ON (deleted.cbFlag & 1) <> 1 AND deleted.cbMarq = inserted.cbMarq WHERE (inserted.cbFlag & 1) = 1 AND NOT EXISTS(SELECT 'a' FROM cbSysLogRecord WHERE cbFile = 'F_DOCREGL' AND cbSysLogRecord.cbMarq = inserted.cbMarq);
END;
END;
END
ELSE
BEGIN
IF (SELECT COUNT(*) FROM inserted WHERE (cbFlag & 1) = 1) > 0
BEGIN
SELECT @sGUID = CB_LocalGUID FROM cbSysTable WHERE CB_Type = 'CIAL';
UPDATE cbSysLogRecord SET CB_Operation = 2,CB_DateOperation = GetDate(),CB_Creator = inserted.cbCreateur,CB_Replication = inserted.cbReplication,CB_SiteOrigine = @sGUID FROM inserted WHERE cbFile = 'F_DOCREGL' AND cbSysLogRecord.cbMarq = inserted.cbMarq AND (inserted.cbFlag & 1) = 1;
END;
END;
IF UPDATE(DR_No)
IF (select count(*) FROM inserted JOIN deleted ON not deleted.DR_No is null and deleted.DR_No <> 0 and inserted.DR_No <> deleted.DR_No and inserted.cbMarq = deleted.cbMarq) > 0
Raiserror(80011,11,1);
IF UPDATE(EC_No)
UPDATE F_DOCREGL SET cbEC_No = NULLIF(inserted.EC_No,0) FROM inserted WHERE F_DOCREGL.cbMarq = inserted.cbMarq;
IF UPDATE(CA_No)
UPDATE F_DOCREGL SET cbCA_No = NULLIF(inserted.CA_No,0) FROM inserted WHERE F_DOCREGL.cbMarq = inserted.cbMarq;
END TRY
BEGIN CATCH
DECLARE
@ErrorMessage nvarchar(max),
@ErrorNumber int,
@ErrorSeverity int,
@ErrorState int;
SET @ErrorMessage = ERROR_MESSAGE();
SET @ErrorNumber = ERROR_NUMBER();
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorState = ERROR_STATE();
IF XACT_STATE() <> 0
ROLLBACK;
IF (@ErrorNumber = 1205) OR (@ErrorNumber = 1222)
RAISERROR(80008,@ErrorSeverity,@ErrorState);
ELSE
IF @ErrorNumber > 50000
RAISERROR(@ErrorNumber,@ErrorSeverity,@ErrorState);
ELSE
RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState);
RETURN;
END CATCH;
END;
GO |
Partager