CREATE TRIGGER CustomerDelete ON Customer INSTEAD...

Программирование в Microsoft SQL Server 2000

CREATE TRIGGER CustomerDelete   ON Customer   INSTEAD OF DELETE AS   SET NOCOUNT ON  IF ( EXISTS (    SELECT *    FROM DELETED JOIN Sale      ON DELETED.CustId =  Sale.CustId    WHERE DATEDIFF( DAY, SaleDate, CURRENT_TIMESTAMP ) <365 ) )   BEGIN    RAISERROR( 'One or more customers have recent sales', 16, 1)     RETURN  END  DELETE    FROM Sale WHERE CustId IN          ( SELECT CustId            FROM DELETED )   IF ( @@ERROR > 0 ) BEGIN     ROLLBACK    RAISERROR( 'Could not delete all old sales', 16, 2 )     RETURN   END  DELETE  FROM Customer WHERE CustId IN          ( SELECT CustId            FROM DELETED )IF ( @@ERROR > 0 ) BEGIN  ROLLBACK  RAISERROR( 'Could not delete all old customers', 16, 3 )  RETURNENDRETURN

Данный сценарий выполняет действия:

(Отметьте один правильный вариант ответа.)

Варианты ответа

проверяется, имеются ли в таблице Sale прошлогодние заказы удаляемых клиентов, затем удаляются соответствующие строки таблицы Sale, т.к. все они устарели, удаляются клиенты и, в случае неудачи, отменяются все изменения(Верный ответ)

удаляются соответствующие строки таблицы Sale, т.к. все они устарели, в случае неудачи, отменяются все изменения, затем проверяется, имеются ли в таблице Sale прошлогодние заказы удаляемых клиентов, затем удаляются клиенты

удаляются соответствующие строки таблицы Sale, т.к. все они устарели, затем проверяется, имеются ли в таблице Sale прошлогодние заказы удаляемых клиентов, затем удаляются клиенты и, в случае неудачи, отменяются все изменения

Похожие вопросы

CREATE TRIGGER CustomerDelete   ON Customer   INSTEAD OF DELETE AS   SET NOCOUNT ON  IF ( EXISTS (    SELECT *    FROM DELETED JOIN Sale      ON DELETED.CustId =  Sale.CustId    WHERE DATEDIFF( DAY, SaleDate, CURRENT_TIMESTAMP ) < 365 ) )   BEGIN    RAISERROR( 'One or more customers have recent sales', 16, 1)     RETURN  END  DELETE    FROM Sale WHERE CustId IN          ( SELECT CustId            FROM DELETED )   IF ( @@ERROR > 0 ) BEGIN     ROLLBACK    RAISERROR( 'Could not delete all old sales', 16, 2 )     RETURN   END  DELETE  FROM Customer WHERE CustId IN          ( SELECT CustId            FROM DELETED )IF ( @@ERROR > 0 ) BEGIN  ROLLBACK  RAISERROR( 'Could not delete all old customers', 16, 3 )  RETURNENDRETURN

Данный сценарий выполняет действия:

CREATE TRIGGER CustomerDelete   ON Customer   INSTEAD OF DELETE AS   SET NOCOUNT ON  IF ( EXISTS (    SELECT *    FROM DELETED JOIN Sale      ON DELETED.CustId =  Sale.CustId    WHERE DATEDIFF( DAY, SaleDate, CURRENT_TIMESTAMP ) < 365 ) )   BEGIN    RAISERROR( 'One or more customers have recent sales', 16, 1)     RETURN  END  DELETE    FROM Sale WHERE CustId IN          ( SELECT CustId            FROM DELETED )   IF ( @@ERROR > 0 ) BEGIN     ROLLBACK    RAISERROR( 'Could not delete all old sales', 16, 2 )     RETURN   END  DELETE  FROM Customer WHERE CustId IN          ( SELECT CustId            FROM DELETED )IF ( @@ERROR > 0 ) BEGIN  ROLLBACK  RAISERROR( 'Could not delete all old customers', 16, 3 )  RETURNENDRETURN

Данный сценарий выполняет действия:

CREATE TRIGGER TrackCustomerUpdates   ON AppDta.dbo.Customer   FOR INSERT, UPDATE, DELETE AS  DECLARE @InsertedCount INT   DECLARE @DeletedCount INT  SET @InsertedCount = ( SELECT COUNT (*) FROM INSERTED )   SET @DeletedCount =  ( SELECT COUNT(*) FROM DELETED )  IF ( @InsertedCount > 0 ) BEGIN     INSERT INTO AppDta.dbo.CustUpdLog           ( CustId, Action, UpdUser, UpdDateTime )     SELECT CustId,       CASE        WHEN ( @DeletedCount > 0 ) THEN 'Update'         ELSE 'Insert'       END,    CURRENT_USER, CURRENT_TIMESTAMP     FROM INSERTED  END  ELSE IF ( @DeletedCount > 0 ) BEGIN     INSERT INTO AppDta.dbo.CustUpdLog ( CustId, Action, UpdUser, UpdDateTime )       SELECT CustId, 'Delete', CURRENT_USER, CURRENT_TIMESTAMP       FROM DELETED   END

Данный сценарий выполняет действия:

CREATE TRIGGER TrackCustomerUpdates   ON AppDta.dbo.Customer   FOR INSERT, UPDATE, DELETE AS  DECLARE @InsertedCount INT   DECLARE @DeletedCount INT  SET @InsertedCount = ( SELECT COUNT (*) FROM INSERTED )   SET @DeletedCount =  ( SELECT COUNT(*) FROM DELETED )  IF ( @InsertedCount > 0 ) BEGIN     INSERT INTO AppDta.dbo.CustUpdLog           ( CustId, Action, UpdUser, UpdDateTime )     SELECT CustId,       CASE        WHEN ( @DeletedCount > 0 ) THEN 'Update'         ELSE 'Insert'       END,    CURRENT_USER, CURRENT_TIMESTAMP     FROM INSERTED  END  ELSE IF ( @DeletedCount > 0 ) BEGIN     INSERT INTO AppDta.dbo.CustUpdLog ( CustId, Action, UpdUser, UpdDateTime )       SELECT CustId, 'Delete', CURRENT_USER, CURRENT_TIMESTAMP       FROM DELETED   END

Данный сценарий выполняет действия:

CREATE TRIGGER TrackCustomerUpdates   ON AppDta.dbo.Customer   FOR INSERT, UPDATE, DELETE AS  DECLARE @InsertedCount INT   DECLARE @DeletedCount INT  SET @InsertedCount = ( SELECT COUNT (*) FROM INSERTED )   SET @DeletedCount =  ( SELECT COUNT(*) FROM DELETED )  IF ( @InsertedCount > 0 ) BEGIN     INSERT INTO AppDta.dbo.CustUpdLog           ( CustId, Action, UpdUser, UpdDateTime )     SELECT CustId,       CASE        WHEN ( @DeletedCount > 0 ) THEN 'Update'         ELSE 'Insert'       END,    CURRENT_USER, CURRENT_TIMESTAMP     FROM INSERTED  END  ELSE IF ( @DeletedCount > 0 ) BEGIN     INSERT INTO AppDta.dbo.CustUpdLog ( CustId, Action, UpdUser, UpdDateTime )       SELECT CustId, 'Delete', CURRENT_USER, CURRENT_TIMESTAMP       FROM DELETED   END

Данный сценарий выполняет действия:

CREATE FUNCTION CustDiscount   ( @CustId INT )   RETURNS DEC( 5, 3 ) BEGIN  DECLARE @Discount DEC( 5, 3 )   SET @Discount =    ( SELECT Discount       FROM Customer       WHERE CustId = @CustId )   RETURN @Discount END

Данный сценарий выполняет действия:

CREATE FUNCTION CustDiscount   ( @CustId INT )   RETURNS DEC( 5, 3 ) BEGIN  DECLARE @Discount DEC( 5, 3 )   SET @Discount =    ( SELECT Discount       FROM Customer       WHERE CustId = @CustId )   RETURN @Discount END

Данный сценарий выполняет действия:

CREATE FUNCTION CustDiscount  ( @CustId INT )   RETURNS DEC( 5, 3 )BEGIN  DECLARE @Discount DEC( 5, 3 )   SET @Discount =    ( SELECT Discount       FROM Customer       WHERE CustId = @CustId )   RETURN @Discount END

Данный сценарий выполняет действия:

DECLARE @CustId INT DECLARE @CustName VARCHAR( 30 ) DECLARE CustCursor CURSOR FOR   SELECT CustId, Name   FROM Customer   ORDER BY CustId   FOR READ ONLY OPEN CustCursor WHILE (0 = 0) BEGIN   FETCH NEXT    FROM CustCursor     INTO @CustId, @CustName   IF ( @@FETCH_STATUS <> 0 ) BREAK  PRINT CAST ( @CustId AS VARCHAR(IO) ) + ' ' + @CustName ENDCLOSE CustCursor DEALLOCATE CustCursor

Данный сценарий выполняет действия:

DECLARE @CustId INT DECLARE @CustName VARCHAR( 30 ) DECLARE CustCursor CURSOR FOR   SELECT CustId, Name   FROM Customer   ORDER BY CustId   FOR READ ONLY OPEN CustCursor WHILE (0 = 0) BEGIN   FETCH NEXT    FROM CustCursor     INTO @CustId, @CustName   IF ( @@FETCH_STATUS <> 0 ) BREAK  PRINT CAST ( @CustId AS VARCHAR(IO) ) + ' ' + @CustName ENDCLOSE CustCursor DEALLOCATE CustCursor

Данный сценарий выполняет действия: