Ensurepass

QUESTION 21

You need to identify, within a given clause, if the month of February will contain 29 days for a specified year. Which object should you use?

 

A.

DML trigger

B.

Stored procedure

C.

Table-valued function

D.

Scalar-valued function

 

Correct Answer: D

 

 

QUESTION 22

You are creating a function that references a table.

 

You need to prevent the table from being dropped.

 

Which option should you use when you create the function?

 

A.

WITH ENCRYPTION

B.

WITH EXECUTE AS

C.

WITH SCHEMABINDING

D.

WITH RETURNS NULL ON NULL INPUT

 

Correct Answer: C

 

QUESTION 23

Click the Exhibit button.

 

image003

 

You are developing a database using Microsoft SQL Server 2008. The database contains the tables shown in the exhibit.

 

You are required to prevent parts from being deleted if they belong to a kit. If a part belongs to a kit, the delete should not occur and the IsDeleted column for the row should be changed to ‘True’. Parts can be deleted if they do not belong to a kit.

 

You have the following Transact-SQL statement to be used in a trigger:

 

image004

 

You need to implement the Transact-SQL statement in a trigger.

 

Which trigger syntax should you use?

 

A.

CREATE TRIGGER tr_Part_d ON Part

AFTER DELETE AS

BEGIN

END

B.

CREATE TRIGGER tr_Part_d ON Part

INSTEAD OF DELETE AS

BEGIN

END

C.

CREATE TRIGGER tr_KitPart_d ON KitPart

AFTER DELETE AS

BEGIN

END

D.

CREATE TRIGGER tr_KitPart_d ON KitPart

INSTEAD OF DELETE AS

BEGIN

END

 

Correct Answer: B

 

 

 

QUESTION 24

You have a third-party application that inserts data directly into a table. You add two new columns to the table. These columns cannot accept NULL values and cannot use default constraints. You need to ensure that the new columns do not break the third-party application. What should you do?

 

A.

Create a DDL trigger.

B.

Create a stored procedure.

C.

Create an AFTER INSERT trigger.

D.

Create an INSTEAD OF INSERT trigger.

 

Correct Answer: D

 

QUESTION 25

Your database contains two tables named Order and OrderDetails that store order information. They relate to each other using the OrderID column in each table. Your business requires that the LastModifiedDate column in the Order table must reflect the date and time when a change is made in the OrderDetails table for the related order. You need to create a trigger to implement this business requirement. Which Transact-SQL statement should you use?

 

A.

CREATE TRIGGER [uModDate] ON [OrderDetails]

INSTEAD OF UPDATE FOR REPLICATION

AS

UPDATE [Order]

SET [LastModifiedDate] = GETDATE()

FROM inserted

WHERE inserted.[OrderID] = [Order].[OrderID];

B.

CREATE TRIGGER [uModDate] ON [Order]

INSTEAD OF UPDATE NOT FOR REPLICATION

AS

UPDATE [Order]

SET [LastModifiedDate] = GETDATE()

FROM inserted

WHERE inserted.[OrderID] = [Order].[OrderID];

C.

CREATE TRIGGER [uModDate] ON [Order]

AFTER UPDATE FOR REPLICATION

AS

UPDATE [Order]

SET [LastModifiedDate] = GETDATE()

FROM inserted

WHERE inserted.[OrderID] = [Order].[OrderID];

D.

CREATE TRIGGER [uModDate] ON [OrderDetails]

AFTER UPDATE NOT FOR REPLICATION

AS

UPDATE [Order]

SET [LastModifiedDate] = GETDATE()

FROM inserted

WHERE inserted.[OrderID] = [Order].[OrderID];

 

Correct Answer: D

 

 

 

 

QUESTION 26

You need to ensure that tables are not dropped from your database. What should you do?

 

A.

Create a DDL trigger that contains COMMIT.

B.

Create a DML trigger that contains COMMIT.

C.

Create a DDL trigger that contains ROLLBACK.

D.

Create a DML trigger that contains ROLLBACK.

 

Correct Answer: C

Explanation:

DDL triggers can execute either when a DDL statement is executed or when the user logs on to the SQL Server instance.

DDL triggers can be scoped at either the database or instance level. To scope a DDL trigger at the instance level, you use the ON ALL SERVER option. To scope a DDL trigger at the database level, you use the ON DATABASE option.

 

The following is an example of a DDL trigger:

 

CREATE TRIGGER tddl_tabledropalterprevent

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

AS

PRINT ‘You are attempting to drop or alter tables in production!’

ROLLBACK;

 

Almost all DDL commands run within the context of a transaction. Because a DDL trigger also runs within the same transaction context, any DDL statement running in the context of a transaction can be rolled back.

 

 

QUESTION 27

You are responsible for a SQL Server database. You require the tables to be added or altered only on the first day of the month. You need to ensure that if the tables are attempted to be modified or created on any other day, an error is received and the attempt is not successful. Which Transact-SQL statement should you use?

 

A.

CREATE TRIGGER TRG_TABLES_ON_FIRST

ON DATABASE FOR CREATE_TABLE

AS

IF DATEPART(day,getdate())>1

BEGIN

RAISERROR (‘Must wait til next month.’, 16, 1)

END

B.

CREATE TRIGGER TRG_TABLES_ON_FIRST

ON DATABASE FOR CREATE_TABLE,ALTER_TABLE

AS

IF DATEPART(day,getdate())>1

BEGIN

RAISERROR (‘Must wait til next month.’, 16, 1)

END

C.

CREATE TRIGGER TRG_TABLES_ON_FIRST

ON DATABASE FOR CREATE_TABLE,ALTER_TABLE

AS

IF DATEPART(day,getdate())>1

BEGIN

ROLLBACK

RAISERROR (‘Must wait til next month.’, 16, 1)

END

D.

CREATE TRIGGER TRG_TABLES_ON_FIRST

ON ALL SERVER FOR ALTER_DATABASE

AS

IF DATEPART(day,getdate())>1

BEGIN

ROLLBACK

RAISERROR (‘Must wait til next month.’, 16, 1)

END

 

Correct Answer: C

 

 

QUESTION 28

You have a single CLR assembly in your database. The assembly only references blessed assemblies from the Microsoft .NET Framework and does not access external resources. You need to deploy this assembly by using the minimum required permissions. You must ensure that your database remains as secure as possible. Which options should you set?

 

A.

PERMISSION_SET = SAFE

TRUSTWORTHY ON

B.

PERMISSION_SET = SAFE

TRUSTWORTHY OFF

C.

PERMISSION_SET = UNSAFE

TRUSTWORTHY ON

D.

PERMISSION_SET = EXTERNAL_ACCESS

TRUSTWORTHY OFF

 

Correct Answer: B

 

QUESTION 29

You have created an assembly that utilizes unmanaged code to access external resources. You need to deploy the assembly with the appropriate permissions. Which permission set should you use?

 

A.

SAFE

B.

UNSAFE

C.

EXTERNAL_ACCESS

D.

Default permission set

 

Correct Answer: B

 

QUESTION 30

You have tables named Products and OrderDetails. The Products table has a foreign key relationship with the OrderDetails table on the ProductID column. You have the following Transact-SQL batch:

 

BEGIN TRY

 

BEGIN TRANSACTION

 

DELETE FROM Products WHERE ProductID = 5;

 

BEGIN TRANSACTION

 

INSERT INTO OrderDetails

 

( OrderID, ProductID, Quantity )

 

VALUES

 

( 1234, 5, 12 );

 

COMMIT TRANSACTION

 

COMMIT TRANSACTION

 

END TRY

 

BEGIN CATCH

 

ROLLBACK TRANSACTION

 

PRINT ERROR_MESSAGE();

 

END CATCH

 

You need to analyze the result of executing this batch. What should be the expected outcome?

 

A.

1. The product will be deleted from the Products table.

2. The order details will be inserted into the OrderDetails table.

B.

1. The product will be deleted from the Products table.

2. The order details will not be inserted into the OrderDetails table.

C.

1. The product will not be deleted from the Products table.

2. The order details will be inserted into the OrderDetails table.

D.

1. The product will not be deleted from the Products table.

2. The order details will not be inserted into the OrderDetails table.

 

Correct Answer: D

Explanation:

ROLLBACK { TRAN | TRANSACTION }

[ transaction_name | @tran_name_variable

| savepoint_name | @savepoint_variable ]

[ ; ]

transaction_name

Is the name assigned to the transaction on BEGIN TRANSACTION. When nesting transactions,

transaction_name must be the name from the outermost BEGIN TRANSACTION statement.

savepoint_name

Is savepoint_name from a SAVE TRANSACTION statement. Use savepoint_name when a conditional rollback should affect only part of the transaction.

 

ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement. In both cases, ROLLBACK TRANSACTION decrements the @@TRANCOUNT system function to 0. ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT.

 

A transaction cannot be rolled back after a COMMIT TRANSACTION statement is executed, except when the COMMIT TRANSACTION is associated with a nested transaction that is contained within the transaction being rolled back. In this instance, the nested transaction will also be rolled back, even if you have issued a COMMIT TRANSACTION for it.

SQL Server 2008 error handling best practice

CREATE PROCEDURE SaveTranExample

@InputCandidateID INT

AS – Detect whether the procedure was called from an active transaction and save that for later use. – In the procedure, @hasOuterTransaction = 0 means there was no active transaction — and the procedure started one. – @hasOuterTransaction > 0 means an active transaction was started before the — procedure was called.

DECLARE @hasOuterTransaction BIT = CASE WHEN @@TRANCOUNT > 0 THEN 1 ELSE 0

END;

 – Save points need unique names if modules can nest otherwise you can rollback — to the wrong save point. The solution is to use a GUID to name the save points. DECLARE @rollbackPoint nchar(32) = REPLACE(CONVERT(NCHAR(36), NEWID()), N’-‘, N”);

IF @hasOuterTransaction > 0

BEGIN – Procedure called when there is an active transaction. — Create a savepoint to be able to roll back only the work done in the procedure if there is an error.

SAVE TRANSACTION @rollbackPoint;

END

ELSE – Procedure must start its own transaction.

BEGIN TRANSACTION @rollbackPoint; – Modify database.

BEGIN TRY – Do work;

DELETE HumanResources.JobCandidate

WHERE JobCandidateID = @InputCandidateID; – Get here if no errors; must commit – any transaction started in the – procedure, but not commit a transaction – started before the transaction was called.

IF @hasOuterTransaction = 0

BEGIN – @hasOuterTransaction = 0 means no transaction was started before the procedure was called. – The procedure must commit the transaction it started.

COMMIT TRANSACTION;

END

END TRY

BEGIN CATCH – An error occurred; – If the transaction is still valid

IF XACT_STATE() = 1 – The XACT_STATE function can return the following values:

– 1 An open transaction exists that can be either committed or rolled back.

– 0 There is no open transaction.

– -1 An open transaction exists, but it is in a doomed state. Due to the type of error that was raised, the transaction can only be rolled back.

 

BEGIN

– Because the syntax for ROLLBACK TRANSACTION is the same for the transaction and for a savepoint

– (ROLLBACK TRANSACTION [ transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable ])

 

– we can write the following:

ROLLBACK TRANSACTION @rollbackPoint;

– In case @rollbackPoint has the name of a transaction, roll back to the beginning of the transaction.

– In case @rollbackPoint has the name of a savepoint, roll back to the savepoint.

END;

ELSE IF XACT_STATE() = -1

BEGIN

IF @hasOuterTransaction = 0

BEGIN – Transaction started in procedure. – Roll back complete transaction.

ROLLBACK TRANSACTION;

END

– If the transaction is uncommitable, a rollback to the savepoint is not allowed

– because the savepoint rollback writes to the log. Just return to the caller, which

– should roll back the outer transaction.

END

– Execute Standard module error handler;

– After the appropriate rollback, echo error information to the caller.

DECLARE @ErrorMessage NVARCHAR(4000);

DECLARE @ErrorSeverity INT;

DECLARE @ErrorState INT;

SELECT @ErrorMessage = ERROR_MESSAGE();

SELECT @ErrorSeverity = ERROR_SEVERITY();

SELECT @ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage, — Message text.

@ErrorSeverity, — Severity.

@ErrorState — State.

);

END CATCH

GO

 

Instant Access to Download Latest Complete Collection of Microsoft 70-433 Real Exam

Try Microsoft 70-433 Free Demo