SQL Queries

Trigger exercises.

1. Create an INSTEAD OF UPDATE, DELETE on ProductDescription to print the number of rows attempted to be updated/deleted.

In the case of an update, also print the if the ModifiedDate is being updated.

2. Modify the above trigger to prevent updates or deletes on rows which currently describe ProductModels. Exclude these rows from the update/delete, allow all other rows to be updated/deleted as usual. For rows updated, the ModifiedDate must be set the time of the update ( GETDATE() ).

AdventureWorksLT Schema SalesLT Schema Best Print Results if 8.5x11 paper, Landscape Fit to 1 sheet Dbo Schema PK ProductID U1 Name U2 ProductNumber FK1 U1 Name U2 rowguid ModifiedDate PK ErrorloglD Database Version VersionDate ModifiedDate ErrorNumber CustomerAddress PK,FK2 CustomerlD PK,FK1 AddressID FK2 ProductCategorylD SellStartDate SellEndDate U1 Name ErrorLine U2 rowguid AddressType rowguid ModifiedDate ModifiedDate U1 leName U3 rowguid PK SalesOrderlD PK,FK2 ProductModellD RevisionNumber PK Culture DueDate 1 AddressLine1 11 City 11 StateProvince 1 PostalCode PK CustomerlD U2 SalesOrderNumber Title U1 rowguid FK1,11 CustomerlD ShipMethod ModifiedDate PK LastName PK,FK1 SalesOrderlD SubTotal TaxAmt Freight +TotalDue U1 rowguid Modified Date 11 EmailAddress FK2,11ProductID Key l Index U Unique Index X XML Index +Computed Colum PK Pimary Key FK Foreign Key PasswordHashh Password Salt U1 rowguid LineTotal rowguid ModifiedDate ModifiedDate U1 rowguid U1 FK2 ShipToAddressID FK3 BillToAddressID ModifiedDate